Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
User | Count |
---|---|
98 | |
91 | |
84 | |
72 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |