cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnnyDax2
New Member

DAX Performance issue FILTER

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.

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1669878755941.png

 

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors