Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi ,
We have 21mil rows tables and corrected to PowertBI Desktop using Direct Query method. We have created a parameter table with 7 Measure names and created a measure using swtich case to show different measures as per slicer selection(Dynamic Measure Selection). Measure is show below
= IF (
HASONEVALUE ( 'ViewTrend'[SelectMeasure] ),
SWITCH (
VALUES ( 'ViewTrend'[SelectMeasure] ),
"Total Claims", [Claims],
"Total Incurred", [Total Incurred Amount],
"Medical Incurred", [Total Medical Incurred],
"Indemnity Incurred", [Total Indemnity Incurred],
"Total Paid", [Total Paid Amount],
"Medical Paid", [Total Medical Paid],
"Indemnity Paid", [Total Indemnity Paid],
[Claims]
),
[Claims]
)
But the performace of this measure is very slow and some times visual fails. I would like to know if there is a better way of writing this measure to increase the performance.
Thank you for your help,
Vicky
That looks like the standard disconnected table trick. Not sure what you could do to improve it other than refactoring your report to not require using it.
If you run the measures seperately do they run quickly?
I have not experienced any performance issues when using the Dynamic Measures before (Mostly been done where the data was imported into the Power BI Desktop file)
If I use this measure against 8k rows it is fast but wen I use this measure against 20Mill it is very slow.
That would indicate that the measure is not as fast as it could be. I would suggest looking at ways to improve the measure so that it returns the data faster.
Once that is done, then I am sure that the SWITCH measure would work well.
Sorry - Measure I meant here is Switch case measure.
all the measures used in the switch case are simple sum() and countrows.
independently all measures work pretty good.
I am using the switch case measure across a time period for a year (Trending Chart).
Ok thanks for letting me know.
I am not sure why the SWITCH is so slow. My only thinking is possibly because it is built ontop of the DirectQuery connection?
I dont think direct query will effect a measure performance so much . I woould like to know if there is any other way fo achieving this functionality without using Switch case of hasonefilter function
Are you certain that it is this SWITCH statement that is running slow versus the actual measures that are being displayed. I think that was what @GilbertQ was asking. I tend to agree with him, my suspicion would be that a simple SWITCH statement shouldn't slow things down.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |