Dax Formula Optimization Help

[Syndicate_Admin]

Hello everyone

I need help to see if it is possible to optimize the following DAX formula:

The idea is that when the user changes the what-if parameters that he has on his screen, the formula calculates automatically, depending on the type of analysis (Ecoli, Listeria... ) whether it is positive or not, that is if its result is greater than the number entered by the user in its parameter.

The problem is that I have a lot of data loaded into the tables and the timeout for each visual every time the user makes any changes or applies some filter is very long because the DAX formula with the filter function must traverse my entire dataset.

I'll leave the formula written to you, see if you can help me:

TotalPositive =

var Tablesaux

SELECTCOLUMNS(

FILTER(

MicroHeader,

MicroHeader[CustomerID] = SELECTEDVALUE(MicroHeader[CustomerID])

),

"AuthLabID",MicroHeader[AuthLabID],

"AnalysisID",MicroHeader[AnalysisID],

"sign",MicroHeader[sign],

"AmoutDetected",MicroHeader[AmountDetected]

)

was TPositive =

CALCULATE(DISTINCTCOUNT(MicroHeader[AuthLabID]), MicroHeader[sign] <> "<"

,FILTER(TableAux

,IF([AnalysisID] == 1 && [Total Amount] > [Valor Ecoli PARAM],1

,IF([AnalysisID] == 2 && [Total Amount] > [Valor Ecoli 0157:H7 PARAM],1

,IF([AnalysisID] == 3 && [Total Amount] > [Valor Enterobacteriaceae PARAM],1

,IF([AnalysisID] == 4 && [Total Amount] > [Valor Fecal Coliform PARAM],1

,IF([AnalysisID] == 7 && [Total Amount] > [Valor Listeria PARAM],1

,IF([AnalysisID] == 8 && [Total Amount] > [Valor Mold],1

,IF([AnalysisID] == 9 && [Total Amount] > [Valor Others PARAM],1

,IF([AnalysisID] == 10 && [Total Amount] > [Valor Pseudomonas PARAM],1

,IF([AnalysisID] == 11 && [Total Amount] > [Valor Salmonella PARAM],1

,IF([AnalysisID] == 12 && [Total Amount] > [Valor Shigella PARAM],1

,IF([AnalysisID] == 13 && [Total Amount] > [Valor Staphylococcus aureus PARAM],1

,IF([AnalysisID] == 14 && [Total Amount] > [Valor STEC PARAM],1

,IF([AnalysisID] == 15 && [Total Amount] > [Valor STEC/EC O157:H7 PARAM],1

,IF([AnalysisID] == 16 && [Total Amount] > [Valor Total Coliform PARAM],1

,IF([AnalysisID] == 17 && [Total Amount] > [Valor TPC PARAM],1

,IF([AnalysisID] == 18 && [Total Amount] > [Valor Yeast],1

,0) ))))))))))))))) == 1))

RETURN

IF(Tpositive= BLANK(),0,Tpositive)

Thanks a lot.

[parry2k]

@Syndicate_Admin good luck!

[parry2k · Accepted solution]

@Syndicate_Admin try this, first not sure why you are storing the result  in a table, and seems like you have filter on CustomerID from the same table, without knowing everything try this:

TotalPositive =
var __value = 
SWITCH ( [AnalysisId],
1, [Valor Ecoli PARAM],
2, [Valor Ecoli 0157:H7 PARAM],
3, [Valor Enterobacteriaceae PARAM],
4, [Valor Fecal Coliform PARAM],
7, [Valor Listeria PARAM],
8, [Valor Mold],
9, [Valor Others PARAM],
10, [Valor Pseudomonas PARAM],
11, [Valor Salmonella PARAM],
12, [Valor Shigella PARAM],
13, [Valor Staphylococcus aureus PARAM],
14, [Valor STEC PARAM],
15, [Valor STEC/EC O157:H7 PARAM],
16, [Valor Total Coliform PARAM],
17, [Valor TPC PARAM],1
18, [Valor Yeast]
,0)
var TPositive =
CALCULATE(DISTINCTCOUNT(MicroHeader[AuthLabID]), MicroHeader[sign] <> "<"
FILTER ( MicroHeader, [Total Amount] > __value 
) + 0
RETURN
Tpositive

there might be a syntax error when I typed the code, so fix the code if run into any syntax error.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Syndicate_Admin]

Thank you very much for your suggestion, I tried it in the report and if the formula has already been more optimized, that if there are still several visualizations that have a very high load time and that gets even more complicated with those customers who have a large volume of data.

The reason why I was generating an auxiliary table in the formula, is because in the report the CustomerID field contains the ID of each customer, which will be a value that I will filter by URL, then what I wanted to achieve is that the formula only went through the customer data that I am showing (a specific customerID), instead of having to perform the calculations to the entire dataset I manage , which is more than 20 thousand customers.

For now the only solution I have is to separate the report between customerID and handle less data to speed up the execution of the DAX query when the user interacts with the filters, not if there is a better way to be able to filter the information by customer dynamically, without having to resort to power query parameters , so that this formula only focuses on the client selected by URL.

Thank you for your response, I'll still use that improvement you gave me.