Countx with summarize very slowly

[Juju123]

Hi, 

 I work in DorectQuery Mode. 
i have two tables in my model : 

- Fact table with a lot of row

- Calendar table that is connect to Fact table.

i this following measures in Power BI (they are all measure type columns and not calculated columns) : 

1 - I create a cumulative sum

2 - I create a measure which count number of reference in my model :

DISTINCT REFERENCE = DISTINCTCOUNT(REFERENCE)

3 - i create a third measure which count number of reference with cumul <>0

COUNTX(summarize(FILTER('TABLE_FACT','TABLE_FACT'[RUNNING_TOTAL]<>0),TABLE_FACT[REFERENCE]),[DISTINCT REFERENCE])

My problem is with third measure . When I put this measure in the card , it's take a long time to load (5 minutes). 

Have you an idea for speed up this third measure ? 

Thanks you 

[Ritaf1983 · Accepted solution]

Hi @Juju123 
Distinct count and summarizing tables can cause slow performance.
Please refer to the linked articles with alternatives:
https://gorilla.bi/dax/optimize-distinctcount/

https://insightsquest.com/2016/11/05/dax-query-tuning-example/

you also can download the Power BI DAX studio to test your tries :

https://daxstudio.org/docs/features/load-powerbi-perf-data/

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

[Juju123]

Hi @Ritaf1983, 

Thank you for taking the time to answer my problem and thank you for the 2 links.

Sorry, but I've been on Power Bi for 2 months now so I have questions about optimizing my DAX formula : 

COUNTX(summarize(FILTER('TABLE_FACT','TABLE_FACT'[RUNNING_TOTAL]<>0),TABLE_FACT[REFERENCE]),[DISTINCT REFERENCE])

I used the example that was in the article
DEFINED
MEASURE
MEASURE
EVALUATE
ADDCOLUMNS (
ADDCOLUMNS (
SIMMARTZE
CALCULATETABLE
<TABLE>, <FILTER Params>
“Grouping Columns>
),

'Totals of Individual Buckets'
“expression>.
Totals of
expression
)
based on this query I would like to know how I can optimize my query ? 

Thanks in advance