DAX Performance issue FILTER

[JohnnyDax2]

Hi everyone,

I'm experiencing a performance issue with a dataset.

The use case is to count to number of distinct store that are active.

I used to do DISTINCTCOUNT(Sales[Store_key])

Unfortunatly some store have several store_key because of a specific business context.

Therefore when i display them with the "Store Name" from the dimension "Store" it counted 2 instead of 1

So i changed the formula for this :

# Active Stores = 
VAR _StoreList = FILTER(VALUES(Store[Store Name]),[Sum of Sales]>0)

return countrows(_StoreList)

And this seems to work at first sight, but if i filter the "Store" table and at the same time create a table with the [Store Name] displayed it will not compute, it's just endlessly load and fail at some point.

If i put another dimension (like the day) it will work tho and fast.

Something more strange is that it kind of work on the main dataset (where we develop our measure) on the desktop client. But if i try the same test on a side report, connected to the dataset it will always fails.

[v-yangliu-msft]

Hi  @JohnnyDax2 ,

You can try the following measure:

Flag =
var _table=
SUMMARIZE('Table','Table'[Store],"Sum",SUMX(FILTER(ALL('Table'),'Table'[Store]=EARLIER('Table'[Store])),[Sales]))
var _select=
SELECTCOLUMNS(FILTER(_table,[Sum]>0),"1",[Store])
return
CALCULATE(
    DISTINCTCOUNT([Store_key]),FILTER(ALL('Table'),'Table'[Store] in _select))

Result：

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

In your last two sentences, I'm not quite sure what you mean by whether the expected result is represented in the form of a picture.

Best Regards,

Liu Yang

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