Measure for Master table values

[Anonymous]

Hi All,

I am having a master table where I have all KPI names as a column values (total 44). Also have 44 measures created in another table. There is no linking between these 2 tables so I have created another measure in master table with switch case (with 44 conditions). However, performance is not that good with this approach. Is there any better approach to do this?

Regards,

Niket Talati 

[v-alq-msft]

Hi, @Anonymous 

I think your measure is correct to achieve your requirement. You need to create a corresponding measure for each KPI. I'd like to suggest you use performance analyzer to check if there is something else leading to the slow performance. 

You may also refer to the following documents to imporve your dax formula.

DIVIDE function vs divide operator (/) 

Appropriate use of error functions 

Use SELECTEDVALUE instead of VALUES 

Use COUNTROWS instead of COUNT 

Use variables to improve your formulas 

Avoid converting BLANKs to values 

Column and measure references 

Avoid using FILTER as a filter argument 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Typically switch measures are costly. What is the need to have 44 measures in a Switch, are you creating a measure slicer

[Anonymous]

@amitchandak ,

Thanks for your response. Yes, I am having it as slicer and the names of column on X axis. So that is why I created this big measure.

Regards,

Niket Talati

[amitchandak]

@Anonymous , I think the way you created is correct. This is another example

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

See if these can help

https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Relationships/td-p/712566
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-should-I-do/td-p/712567

[lbendlin]

Do you have usage statistics on these measures (ie do you know how often each of these is actively selected by your audience)?  That should help you to simplify your report or maybe even break it up into smaller pieces.

[Anonymous]

Hi @lbendlin ,

Thanks for your reply. I am using it in 3 different graphs and all KPIs need to be shown at first glance. So I cannot divide it unfortunately.

Regards,

Niket Talati