Need help with measure performance on direct lake model

Brian Szeto

Sep '24 (edited) • Technical

Hi Everyone,

I have created the following measure on a semantic model using direct lake, which looks something like this

Measure = SWITCH(

TRUE(),

SELECTEDVALUE(Transactions[ClassID]) IN {1234, 5678}, SUM(Transactions[Quantity]),

DISTINCTCOUNT(Transactions[TransactionID])

)

The measure works perfectly fine in a PowerBI card visual, but when I try to use the same measure in a table/matrix, any resulting query would basically time out

I've tried the following but nothing seems to work

Use IF instead of SWITCH, made no difference and query still times out
Tried other aggregate functions instead of DISTINCTCOUNT, such as both conditions using SUM, and also made no difference and query still times out
I even tried both conditions doing SUM(Transactions[Quantity]) and it still doesn't work. Measure = SUM(Transactions[Quantity]) works perfectly fine, of course.

Without showing the entire DAX query, when I traced the query in PowerBI desktop for a simple matrix, snippets of the query looks like the below, which eventually times out

VAR __DS0Core =

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL(

ROLLUPGROUP(

'Transactions'[TransactionDisplayName],

'CampaignCategory'[CampaignCategoryName],

'Item'[ItemName],

'Class'[ClassName]

), "IsGrandTotalRowTotal"

__DS0FilterTable,

__DS0FilterTable2,

__DS0FilterTable3,

"SumQuantity", CALCULATE(SUM('silver_Transaction'[Quantity])),

"Measure", 'Transactions'[Measure]

)

I'm not good at DAX, so if anyone can shed some insight on how to rewrite this measure to perform in a table/matrix that would be highly appreciated. Thanks in advance.

3 comments