Optimize 4 Similar Measures in the Same Visual

[dorku]

Hello everyone,

I have a dashboard with multiple visuals, but one specific visual is performing very slowly. This visual uses 4 similar DAX measures, each calculated on the same table but using different columns. All the measures filter out a default value (999) before calculating an average.

Here’s an example of two of the measures:

AVERAGE_SNIF_AD_MESIRA :=
CALCULATE(
AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD]),
FILTER(
V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N,
V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD] <> 999
)
)

AVERAGE_MR_DAVARIM_MESIRA_DAVAR_BD :=
CALCULATE(
AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MR_DAVARIM_MESIRA_DAVAR_BD]),
FILTER(
V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N,
V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MR_DAVARIM_MESIRA_DAVAR_BD] <> 999
)
)

My questions are:

1. Is there a way to combine these similar measures into a single DAX calculation to simplify and optimize the visual?
2. Can I improve performance by modifying the current DAX measures?

Any advice or insights would be greatly appreciated! Thank you!

[DataNinja777 · Accepted solution]

Hi @dorku ,

To address your question, the similar DAX measures can be combined into a single dynamic calculation to simplify your model and improve performance. This can be achieved by introducing a parameter table with relevant column names (e.g., SNIF_AD_MESIRA_BD and MR_DAVARIM_MESIRA_DAVAR_BD) and then creating a DAX measure that calculates the average based on the selected column. Here is the DAX code for a dynamic measure:

AVERAGE_MEASURE :=
VAR SelectedColumn =
    SELECTEDVALUE(
        'Parameter Table'[ColumnName]
    )
RETURN
    CALCULATE(
        AVERAGE(
            SWITCH(
                TRUE(),
                SelectedColumn = "SNIF_AD_MESIRA_BD", V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD],
                SelectedColumn = "MR_DAVARIM_MESIRA_DAVAR_BD", V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MR_DAVARIM_MESIRA_DAVAR_BD],
                BLANK()
            )
        ),
        FILTER(
            V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N,
            SWITCH(
                TRUE(),
                SelectedColumn = "SNIF_AD_MESIRA_BD", V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD] <> 999,
                SelectedColumn = "MR_DAVARIM_MESIRA_DAVAR_BD", V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MR_DAVARIM_MESIRA_DAVAR_BD] <> 999,
                TRUE()
            )
        )
    )

This measure dynamically selects the appropriate column for calculation using the SWITCH function and applies the filtering logic to exclude rows where the value is 999. The Parameter Table allows users to choose the column they want to calculate the average for, making the measure both flexible and reusable.

To optimize your current measures further, you can replace the FILTER function with KEEPFILTERS, which directly applies the filter context and reduces computational overhead. For example:

AVERAGE_SNIF_AD_MESIRA :=
CALCULATE(
    AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD]),
    KEEPFILTERS(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD] <> 999)
)

Additionally, if the value 999 is consistently excluded, consider pre-filtering it in your data model. This can be done in Power Query or by applying a filter at the model level, which reduces the amount of data processed during calculations.

By implementing the dynamic measure and applying these optimizations, you should achieve better performance and a cleaner DAX implementation. If the visual remains slow, it may be necessary to review the dataset size and visual complexity for further enhancements.

Best regards,

[dorku]

First of all, thank you so much for your help and the detailed solution!
However, I encountered an error when trying to publish the measure to my Tabular cube:
Semantic error: The AVERAGE function only accepts a column reference as an argument.

To address this, I modified the measure according to your suggestion, and here's what I came up with:

AVERAGE_MEASURE_2 :=
VAR SelectedColumn =
SELECTEDVALUE('Parameter Table'[ColumnName])
RETURN
SWITCH(
TRUE(),
SelectedColumn = "SNIF_AD_MESIRA_BD",
CALCULATE(
AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD]),
KEEPFILTERS(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD] <> 999)
),
SelectedColumn = "MESHECH_YAMIM_MISLUAH_AD_MR",
CALCULATE(
AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MISLUAH_AD_MR]),
KEEPFILTERS(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MISLUAH_AD_MR] <> 999)
),
SelectedColumn = "MESHECH_YAMIM_MR_AD_SNIF",
CALCULATE(
AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MR_AD_SNIF]),
KEEPFILTERS(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MR_AD_SNIF] <> 999)
),
SelectedColumn = "MR_DAVARIM_MESIRA_DAVAR_BD",
CALCULATE(
AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MR_DAVARIM_MESIRA_DAVAR_BD]),
KEEPFILTERS(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MR_DAVARIM_MESIRA_DAVAR_BD] <> 999)
),
BLANK()
)

I’m not entirely sure if this approach captures the intent correctly. Did I misunderstand anything?

I noticed that when I replace the dynamic parameter table with individual measures using KEEPFILTERS, the performance improves significantly. However, I feel like the parameter table approach could optimize it further and provide better flexibility.
Do you have any additional suggestions or ideas to refine this?

additionally, im losing the interactive with the other visual if i use the table, so if you know how to fix it to its real can help me

thanks again !

[DataNinja777]

Hi @dorku ,

Your modified measure is a good attempt at dynamically calculating averages based on a parameter table. However, there are a few things we can address to improve performance and maintain interactivity with other visuals.

To address the semantic error, the AVERAGE function requires a column reference as an argument, which you have correctly implemented in your updated measure. While your approach works, using SWITCH(TRUE()) with SELECTEDVALUE and recalculating averages can be computationally expensive for large datasets. Instead, consider predefining individual measures for each column and referencing them within your SWITCH statement. This can significantly improve performance.

Here’s a refined measure:

AVERAGE_MEASURE_2 :=
VAR SelectedColumn = SELECTEDVALUE('Parameter Table'[ColumnName])
RETURN
SWITCH(
    TRUE(),
    SelectedColumn = "SNIF_AD_MESIRA_BD",
        [Average_SNIF_AD_MESIRA_BD],
    SelectedColumn = "MESHECH_YAMIM_MISLUAH_AD_MR",
        [Average_MESHECH_YAMIM_MISLUAH_AD_MR],
    SelectedColumn = "MESHECH_YAMIM_MR_AD_SNIF",
        [Average_MESHECH_YAMIM_MR_AD_SNIF],
    SelectedColumn = "MR_DAVARIM_MESIRA_DAVAR_BD",
        [Average_MR_DAVARIM_MESIRA_DAVAR_BD],
    BLANK()
)

Predefine the following measures separately:

Average_SNIF_AD_MESIRA_BD :=
CALCULATE(
    AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD]),
    V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD] <> 999
)

Average_MESHECH_YAMIM_MISLUAH_AD_MR :=
CALCULATE(
    AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MISLUAH_AD_MR]),
    V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MISLUAH_AD_MR] <> 999
)

Average_MESHECH_YAMIM_MR_AD_SNIF :=
CALCULATE(
    AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MR_AD_SNIF]),
    V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MR_AD_SNIF] <> 999
)

Average_MR_DAVARIM_MESIRA_DAVAR_BD :=
CALCULATE(
    AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MR_DAVARIM_MESIRA_DAVAR_BD]),
    V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MR_DAVARIM_MESIRA_DAVAR_BD] <> 999
)

If you are losing interactivity with other visuals, it may be because the parameter table selection is not properly influencing your fact table. To address this, ensure that the Parameter Table is disconnected from the rest of the model and use TREATAS to apply the parameter selection dynamically as a filter on your fact table. Here’s an example of how you can achieve this:

Dynamic_Average :=
VAR SelectedColumn = SELECTEDVALUE('Parameter Table'[ColumnName])
RETURN
SWITCH(
    TRUE(),
    SelectedColumn = "SNIF_AD_MESIRA_BD",
        CALCULATE(
            AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD]),
            TREATAS(
                VALUES('Parameter Table'[SelectedColumn]),
                V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[SNIF_AD_MESIRA_BD]
            )
        ),
    SelectedColumn = "MESHECH_YAMIM_MISLUAH_AD_MR",
        CALCULATE(
            AVERAGE(V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MISLUAH_AD_MR]),
            TREATAS(
                VALUES('Parameter Table'[SelectedColumn]),
                V_DW_DR_FCT_DR_SHLICHIM_TAHANOT_SOFI_N[MESHECH_YAMIM_MISLUAH_AD_MR]
            )
        ),
    BLANK()
)

This approach ensures flexibility while retaining interactivity and improves performance by leveraging pre-calculated measures. Let me know if you encounter any additional issues!

Best regards,

[dorku]

First of all, thank you so much for your help and thoughtful suggestions for improving the measure. I truly appreciate the time and effort you put into providing such a detailed explanation.

After trying to implement the refined approach you suggested, unfortunately, I couldn’t achieve a noticeable improvement in performance. At this stage, I’ve decided to stick with the simpler solution (the second part of your initial suggestion) where I display the four measures on the graph using KEEPFILTERS.

For now, this approach works best for me in terms of performance and simplicity.

Thank you again for all your assistanceI truly value your guidance!