Forum Discussion

Sere_17's avatar
Sere_17
Regular Visitor
3 years ago
Solved

Measure Calculation Using Dynamic Column Names

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 = { ...
Need Help
  • Sere_17's avatar
    Sere_17
    3 years ago

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
Sere_17
Regular Visitor
3 years ago

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.