Dax Measure Performance

[vickyprudhvi]

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

[Greg_Deckler]

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.

[GilbertQ]

Hi @vickyprudhvi

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)

[vickyprudhvi]

@GilbertQ

If I use this measure against  8k rows it is fast but wen I use this measure against 20Mill it is very slow.

[GilbertQ]

Hi @vickyprudhvi

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.

[vickyprudhvi]

@GilbertQ and  @Greg_Deckler

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).

[GilbertQ]

Hi @vickyprudhvi

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?

[vickyprudhvi]

@GilbertQ

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

[Greg_Deckler]

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.