Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
arzukari
Frequent Visitor

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?

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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 

SU18_powerbi_badge

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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 

SU18_powerbi_badge

arzukari
Frequent Visitor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.