Forum Discussion

Juju123's avatar
Juju123
Icon for Helper III rankHelper III
2 years ago
Solved

Countx with summarize very slowly

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 

 

2 Replies

  • 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's avatar
      Juju123
      Icon for Helper III rankHelper III

      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