Forum Discussion

Juju123's avatar
Juju123
Helper III
2 years ago

DirectQuery - COUNTX very slow

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 🙂

2 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    2 years ago

    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 

  • Juju123's avatar
    Juju123
    Helper III
    2 years ago

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