topic DirectQuery - COUNTX very slow in DAX Commands and Tips

DirectQuery - COUNTX very slow

Juju123 — Thu, 02 Nov 2023 21:09:13 GMT

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 🙂

Re: DirectQuery - COUNTX very slow

VahidDM — Fri, 03 Nov 2023 05:04:40 GMT

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

Re: DirectQuery - COUNTX very slow

Juju123 — Fri, 03 Nov 2023 06:50:13 GMT

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])