Forum Discussion
Measure Calculation Using Dynamic Column Names
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...
- Create a DAX function that uses SELECTEDVALUE() and SWITCH() to get the currently selected value and perform an action based on that value.
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!