Slow Running Dax Measure

[Anonymous]

Is someone able to tell me why the below dax measure runs painfully slow? I'm trying to identify the customers (identifier being the BasketID in the sales table) who only purchased 1 product:

Slow Measure:

Transactions (Selected Product) Bought 1 Unit =

CALCULATE(

[Transactions (Selected Product)],

FILTER(

SUMMARIZE(

Sales,

Sales[BasketID],

"Total Units Purchased",

[Units Sold (Selected Product)]

),

[Total Units Purchased] = 1

)

)

Measures within the above slow measure (both of these are lightning fast)

Transactions (Selected Product) =

CALCULATE(

[Total Transactions],

ALL( 'Sku Reference' ),

USERELATIONSHIP( Sales[Sku Number], 'Selected Products'[Sku Number] )

)

Units Sold (Selected Product) =

CALCULATE(

[Total Units Sold],

ALL('Sku Reference'),

USERELATIONSHIP( Sales[Sku Number], 'Selected Products'[Sku Number] )

)

[Anonymous · Accepted solution]

Hi @Anonymous ,

How about create or var a calculated table and filter the calculated table instead like:

Transactions (Selected Product) Bought 1 Unit = 
var tmptable = 
SUMMARIZE(
Sales,
Sales[BasketID],
"Total Units Purchased", 
[Units Sold (Selected Product)]
)
return
CALCULATE(
[Transactions (Selected Product)],
FILTER(
tmptable,
[Total Units Purchased] = 1
)
)

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much! this worked perfectly!

[Greg_Deckler]

First thing that stands out, it is weird to use a SUMMARIZE in a FILTER. Not saying it is wrong, just not common. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490