Very Slow DAX query

[arzukari]

I need help with the below query:

MRN Diff EID:=

var __EID = CALCULATETABLE(SUMMARIZE(table1,table1[FACILITY_MRN], "Distinct EID",DISTINCTCOUNTNOBLANK(table1[EID])),
COVID[EID_Check]<>"EID Not Available")

return

SUMX(FILTER(__EID,[Distinct EID]>1),1)

The idea was to count how many people have multiple sub ids (EID) for their main ID (MRN).

The measure works great. But when added to a visual with the date as the axis, it becomes incredibly slow. any advice on how to tune the above query?

[AlB · Accepted solution]

Hi @arzukari 

Try

MRN Diff EID :=
VAR __EID =
    FILTER (
        DISTINCT ( table1[FACILITY_MRN] ),
        CALCULATE (
            DISTINCTCOUNTNOBLANK ( table1[EID] ),
            COVID[EID_Check] <> "EID Not Available"
        ) > 1
    )
RETURN
    COUNTROWS ( __EID )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[arzukari]

This is amazing. It is so much faster now, thank you.