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