Forum Discussion

rrb2150's avatar
rrb2150
Frequent Visitor
3 years ago
Solved

Measures as Filters

Hi All! I have 4 measures and adding those 4 measures to create a new measure. Can we have a slicer for the measures, to select which measures we want to add for the final measure?  Thank you. 

(I have 4 columns which I am summing to create those 4 measures)

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , rrb2150 

    I don't currently find a way to call a measure by using the measure name, But if we only have 4 measures, we can use the enumeration method to sum all the cases.

    Here are the steps you can refer to :

    (1)This is my test data , I create four measures :

    (2)We need to create a table which contains the name of the measure and index column like this:

     

    (3)We can create a measure like this:

    Total Measure = 
var _rows =COUNTROWS( VALUES('MeasureName'[Name]))
var _value = SUM('MeasureName'[Index])
var _index = VALUES(MeasureName[Index])
return 
SWITCH(  TRUE(),
_rows = 4 , [Measure1]+[Measure2]+[Measure3]+[Measure4],
_rows = 3 , IF( _value =6 , [Measure1]+[Measure2]+[Measure3] , IF( _value =7 , [Measure1]+[Measure2]+[Measure4] , IF( _value = 8 ,[Measure1]+[Measure3]+[Measure4] ,[Measure2]+[Measure3]+[Measure4]))),
_rows =2 , IF( _value=3 ,[Measure1]+[Measure2] , IF(_value=4 ,[Measure1]+[Measure3] , IF( _value =5 && 1 in _index ,[Measure1]+[Measure4] , IF( _value =5 ,[Measure2]+[Measure3] ,IF(
    _value=6,[Measure2]+[Measure4] , IF( _value=7 ,[Measure3]+[Measure4])))))),
_rows=1 , SWITCH( _value ,1,[Measure1] ,2 ,[Measure2] ,3,[Measure3] ,4,[Measure4]))

    (4) Then we can meet your need , the result is as follows:

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

9 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi rrb2150 
    Please try the following measure

    =
SUMX (
    ADDCOLUMNS (
        ALLSELECTED ( 'Measure Slicer'[Measure] ),
        "@Value",
            SWITCH (
                'Measure Slicer'[Measure],
                "Measure A", [Measure A],
                "Measure B", [Measure B],
                "Measure C", [Measure C],
                "Measure D", [Measure D]
            )
    ),
    "@Value"
)
    • rrb2150's avatar
      rrb2150
      Frequent Visitor
      3 years ago

      Thank you for the reply. Can you please let me know, what a Measure Slicer[Measure] refers to?

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Hi rrb2150 

        It is just a table that you can enter manually and just contains the names of the measures. 

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

    Hi , rrb2150 

    I don't currently find a way to call a measure by using the measure name, But if we only have 4 measures, we can use the enumeration method to sum all the cases.

    Here are the steps you can refer to :

    (1)This is my test data , I create four measures :

    (2)We need to create a table which contains the name of the measure and index column like this:

     

    (3)We can create a measure like this:

    Total Measure = 
var _rows =COUNTROWS( VALUES('MeasureName'[Name]))
var _value = SUM('MeasureName'[Index])
var _index = VALUES(MeasureName[Index])
return 
SWITCH(  TRUE(),
_rows = 4 , [Measure1]+[Measure2]+[Measure3]+[Measure4],
_rows = 3 , IF( _value =6 , [Measure1]+[Measure2]+[Measure3] , IF( _value =7 , [Measure1]+[Measure2]+[Measure4] , IF( _value = 8 ,[Measure1]+[Measure3]+[Measure4] ,[Measure2]+[Measure3]+[Measure4]))),
_rows =2 , IF( _value=3 ,[Measure1]+[Measure2] , IF(_value=4 ,[Measure1]+[Measure3] , IF( _value =5 && 1 in _index ,[Measure1]+[Measure4] , IF( _value =5 ,[Measure2]+[Measure3] ,IF(
    _value=6,[Measure2]+[Measure4] , IF( _value=7 ,[Measure3]+[Measure4])))))),
_rows=1 , SWITCH( _value ,1,[Measure1] ,2 ,[Measure2] ,3,[Measure3] ,4,[Measure4]))

    (4) Then we can meet your need , the result is as follows:

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

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

    rrb2150 , Old measure slicer technique of having an independent table can help

    How to create a Measure Slicer: https://youtu.be/b9352Vxuj-M

     

    Field parameters can also help, for the selected measures you have to use code like

    Selected Measure = maxx(filter(Measure ,Measure[Measure Order] = SELECTEDVALUE(Measure[Measure Order])), Measure[Measure])

     

    I have created an example to use the selected value of the Axis slicer in a measure. you can use the same code for the measure slicer and create additional measures based on that

    https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f

    • rrb2150's avatar
      rrb2150
      Frequent Visitor
      3 years ago

      I have created a measure slicer. But my concern is different. I don't want to show the selected measure value.

       

      For my case, let's say I have four columns: A,B,C,D. And I have summed up each to create 4 measures to get the values as 10,20,30, 40(let's say). 

      Now, I want to have E=a+b+c+d But I want the user to select what he wants to add. (a+b+c+d, or b+d, or a+c+d) so the value of e changes from 100 or 60 or 80.

  • rrb2150's avatar
    rrb2150
    Frequent Visitor
    3 years ago

    I have created a measure slicer. But my concern is different. I don't want to show the selected measure value.

     

    For my case, let's say I have four columns: A,B,C,D. And I have summed up each to create 4 measures to get the values as 10,20,30, 40(let's say). 

    Now, I want to have E=a+b+c+d But I want the user to select what he wants to add. (a+b+c+d, or b+d, or a+c+d) so the value of e changes from 100 or 60 or 80.