Forum Discussion

EvanGetsItDone's avatar
EvanGetsItDone
Icon for Helper I rankHelper I
3 years ago
Solved

Measure that would show Multiple Values based on Slicer Selection

Hello, 

I needed help to display both fields with different values within a Measure. Here's the example I created with the same situation. 


For this scenario, I don't know how am I able to display the values if "Both Magnitude and Period" is selected on the slicer. 
I added the expected result on the screenshot (the table below)

If you have a solution with different approach and logic, feel free to share. 🙂

Let me know if you need more details. 

Thanks!


Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    3 years ago

    Hi EvanGetsItDone ,

     

    You cannot use a measure and return two measures out of it. What can be done is you add both Magnitude and Period measures to a table but these measures should have a condition in it that returns blank depending on the slicer selection. The measure will only return blank and not hide its column. As a workaround, you can create a separate table (import/enter data/calc) that will hold the measure names and its groupings and you can create a measure using the measure name column from that table. Example calc table

     

    MeasureTable = 
DATATABLE (
    "Measure Group", STRING,
    "Measure Name", STRING,
    {
        { "Both Magnitude and Period Values", "Magnitude" },
        { "Both Magnitude and Period Values", "Period" },
        { "Magnitude Only", "Magnitude" },
        { "Period Only", "Period" }
    }
)

     

     

    the measure

     

    FieldValue = 
SWITCH (
    SELECTEDVALUE ( MeasureTable[Measure Name] ),
    "Magnitude", 40050,
    "Period", 13479122.76
)

     

     

    Please see attached sample pbix

3 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    3 years ago

    Hi EvanGetsItDone ,

     

    You cannot use a measure and return two measures out of it. What can be done is you add both Magnitude and Period measures to a table but these measures should have a condition in it that returns blank depending on the slicer selection. The measure will only return blank and not hide its column. As a workaround, you can create a separate table (import/enter data/calc) that will hold the measure names and its groupings and you can create a measure using the measure name column from that table. Example calc table

     

    MeasureTable = 
DATATABLE (
    "Measure Group", STRING,
    "Measure Name", STRING,
    {
        { "Both Magnitude and Period Values", "Magnitude" },
        { "Both Magnitude and Period Values", "Period" },
        { "Magnitude Only", "Magnitude" },
        { "Period Only", "Period" }
    }
)

     

     

    the measure

     

    FieldValue = 
SWITCH (
    SELECTEDVALUE ( MeasureTable[Measure Name] ),
    "Magnitude", 40050,
    "Period", 13479122.76
)

     

     

    Please see attached sample pbix

    • EvanGetsItDone's avatar
      EvanGetsItDone
      Icon for Helper I rankHelper I
      3 years ago

      Hey,

      Thanks for your response, this is a great logic and approach. However I don't think this works if the I use a measure or aggregate (in this case i SUM() the values) on the Switch (FieldValue measure). The grouping seems to only work if the else statement is String like what you did.

       



      • EvanGetsItDone's avatar
        EvanGetsItDone
        Icon for Helper I rankHelper I
        3 years ago

        My Bad, I was not able to put the Measure Group on the Column field the reason it was just showing blank. I will accept this as solution as it helped me solved this issue. Thank you!