Forum Discussion

arzukari's avatar
arzukari
Frequent Visitor
5 years ago
Solved

Very Slow DAX query

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's avatar
    AlB
    5 years ago

    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 

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      arzukari
      Frequent Visitor
      5 years ago

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