Measure Calculation Using Dynamic Column Names

[Sere_17]

I am trying to find a way to create a measure that will sum a column of data in a table based on the item selected in a slicer.

The slicer is based on a field parameter:

Figure 1 Y Axis = {

    ("CDiff_count", NAMEOF('spotfire_rpt HCAI'[CDiff_count]), 0),

    ("EColi_count", NAMEOF('spotfire_rpt HCAI'[EColi_count]), 1),

    ("MRSA_count", NAMEOF('spotfire_rpt HCAI'[MRSA_count]), 2),

    ("MSSA_count", NAMEOF('spotfire_rpt HCAI'[MSSA_count]), 3)

}

Creates this slicer:

What I would like is for the Y Axis of a bar chart to sum a column of data based on the selection made here. The columns have the same name as the options (so there is a column called CDiff_count, EColi_count etc.).

I would prefer to not have to do this using multiple measures or a measure that uses an if statement to check the selected item, as we will likely add more columns later and I want to keep the amount of maintenance required to a minimum.

I assume that there is a way to pass a string as a column name or use a parameter as a column name in DAX that I just haven't come across.

Thanks,

[Sere_17 · Accepted solution]

Just as an update in case anyone comes across the same issue later.

I ended up using Field Parameters to create two slicers (one for infection type and one for bed days).

• Create a DAX function that uses SELECTEDVALUE() and SWITCH() to get the currently selected value and perform an action based on that value.
  • You cannoy use SELECTEDVALUE() on Field Parameter columns, so I created a new column on the Field Parameter data table (selectedMeasure = 'Figure 1 Calculations'[Figure 1 Calculations])
• This new column was then used in aSWITCH() statement to sum the relevant column

infectionSwitchStatement =

var Infection =

SWITCH (

    SELECTEDVALUE('Figure 1 Calculations'[selectedMeasure]),

    "Cdiff", sum('spotfire_rpt v_HED_HCAI_PowerBI'[CDIFF]),

    "EColi", sum('spotfire_rpt v_HED_HCAI_PowerBI'[ECOLI]),

    "MRSA", sum('spotfire_rpt v_HED_HCAI_PowerBI'[MRSA]),

    "MSSA",sum('spotfire_rpt v_HED_HCAI_PowerBI'[MSSA])

)

return Infection

• Repeated column creation and switch statement for the other field parameter I had created.
• Created a new measure on my main data table that divided my selected numerator by my selected denominator.
• Put that new measure into the Y Axis of the bar chart.

So because DAX does not handle using strings or parameters as column names, I needed to repeat the same code for each of the columns I wanted summed. Not my preference, as I am able to use parameters as column names in Spotfire currently, but hopefully that is a feature they will add at some point...

[Sere_17]

I managed to somewhat get around this by using the field parameters as values in a Python visual, but this won't work for me due to the fact that it makes a static Python visual and not a flexible Power BI visual. 

Two field parameters: Figure 1 X Axis and Figure 1 Y Axis.

Add these as values to the python visual.

Also add all possible columns that I want to sum (CDiff_count, EColi_count, MRSA_count, MSSA_count).

The column names that are added to the Python visual depend on which option is selected. So if "Cdiff_count" is selected, it will create a column CDiff_count. 

Ensure that the Figure 1 Y Axis value is above the summed columns.

The Python visualisation will automatically name the selected column to be summed columnname+".1" so as to avoid duplicate names.

Use a for loop to find the duplicated column name. That column is to be plotted as the Y Axis.

Use the list function in python to get a list of all column names. Because of the ordering of the values, I know that the second item in the list will be the x axis.

Use the plot function with the created x axis and y axis variables.

With this, the Python visual will update whenever I make a change to the item selected for Figure 1 X Axis or Figure 1 Y Axis, however the resulting visual functionality isn't appropriate for this situation.

Back to the drawing board!

[v-yadongf-msft]

Hi @Sere_17 ,

This is my test table:

Create three measures for three columns:

Sum_Value1 = SUM('Table'[Value1])

Sum_Value2 = SUM('Table'[Value2])

Sum_Value3 = SUM('Table'[Value3])

Create field parameter:

Is this the result you want?

Best regards,

Yadong Fang

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

[Sere_17]

Hi,

Thank you for your answer. That is the desired output, but I was hoping to find a way to do that without having to create those three separate measures, if that makes sense? So a way to have the measure change which column it is trying to sum based on the selection in that field parameter.

Thanks.

[v-yadongf-msft]

Hi @Sere_17 ,

I think field parameter is already the most flexible and most efficient way. You may need to pivot the data if you don't want to create seperate measures. However, this method is more  troublesome.

Best regards,

Yadong Fang

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

[amitchandak]

@Sere_17 , In both these blogs I have checked for selected value

Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf

you can get more than one value like

Selected Measure = concatenatex(FILTER('Measure Slicer', 'Measure Slicer'[Measure Slicer Order] in values('Measure Slicer'[Measure Slicer Order] )), 'Measure Slicer'[Measure Slicer])

you can change that as per need

[Sere_17]

Hi,

Thank you for replying. I have taken a look through those blogs and my understanding is that I would still need to make separate measures for each of the options e.g. in your blog you made measure Top2 Brand and Top2 Category.

As the calculation is the same across each of the "Figure 1 Y Axis" options, I was hoping to find a way to not create multiple measures.
The conditional formatting blog looks very useful (even for outside of this task), so thank you for sharing.