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

v-yadongf-msft's avatar
v-yadongf-msft
Icon for Community Support rankCommunity Support
3 years ago

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

    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's avatar
      v-yadongf-msft
      Icon for Community Support rankCommunity Support
      3 years ago

      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.