Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Ronie Soriano]

how to convert to dax measure below sql query..thank you

select count(Encounter_No) as CASH_IPD
from
(
select distinct
Encounter_No,
–Registration_No,
–Company_Code,
–Doctor_Code,
Payment_TypeName,
InvoiceNo
from BI_OSR_Revenues
where Tran_Month = 3 and Tran_Year = 2022
and Payment_TypeName = ‘CASH (IPD)’
and isnull(admission_Date,”) <> ”
) as t1

——————————
Ronie Soriano
——————————

[Tomas Torp]

I think this measure should give you the same result. You might want to remove KEEPFILTERS if you want the filter arguments in the CALCULATE-function to override external filters instead of adding to them, and you might want to remove some of the filter arguments completely from the measure and control those filters with a slicer instead:

CASH_IPD =
CALCULATE (
    COUNTROWS (
        SUMMARIZE (
            BI_OSR_Revenues,
            BI_OSR_Revenues[Encounter_No],
            -- BI_OSR_Revenues[Registration_No],
            -- BI_OSR_Revenues[Company_Code],
            -- BI_OSR_Revenues[Doctor_Code],
            BI_OSR_Revenues[Payment_TypeName],
            BI_OSR_Revenues[InvoiceNo]
        )
    ),
    KEEPFILTERS ( BI_OSR_Revenues[Tran_Month] = 3 ),
    KEEPFILTERS ( BI_OSR_Revenues[Tran_Year] = 2022 ),
    KEEPFILTERS ( BI_OSR_Revenues[Payment_TypeName] = "CASH (IPD)" ),
    KEEPFILTERS ( NOT ( ISBLANK ( BI_OSR_Revenues[admission_Date] ) ) )
)?

——————————
Tomas
——————————
——————————————-

[Ronie Soriano]

Thank you sir and is working perfectly

——————————
Ronie Soriano
——————————
——————————————-