DirectQuery - COUNTX very slow

[Juju123]

Hi expert, 

I use function COUNTX in formula : 

Number reference with a gap = COUNTX(FILTER(TABLE,[MEASURE]<>0),[REFERENCE])

This formula is then used in a chart.
However, the graph takes a long time to respond.

Is there a DAX formula that would allow me to obtain my result more quickly or another way to do it?

Thanks 🙂

[Juju123]

Hi @VahidDM ,

Thanks for your feedback.

Indeed I have a lot of data in my table and I have sensitive data.

I created all my measures in Power BI (they are all measure type columns and not calculated columns).

For this measurement: Number reference with a gap = COUNTX(FILTER(TABLE,[MEASURE]<>0),[REFERENCE])

1. I first created a “cumulative” measure

2. I created another measurement that calculates the distinct reference number.

DISTINCT REFERENCE = DISTINCTCOUNT(REFERENCE)

3. I have just created this measure to calculate the measures whose accumulation is different from zero.

I changed DAX formula like this , but time it's no better

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

[VahidDM]

Hi @Juju123 

It depends !

How big is your model?
What is the [Measure] expression?

Can you share a sample of your data table (few rows and all columns) if that does not have any sensetive data?
If your table is big, why not to only keep [REFERENCE] with Values using the same filter?

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube