Forum Discussion

mogugu_84's avatar
mogugu_84
Helper I
1 year ago
Solved

Switch measure using parameter in SUM formulas

Hi 

 

i have a created a parameter (called "choose measure" to switch between unit and cash) and it works for simple charts with no calculated field

 

the parameter looks like this:

 

Choose measure = {
    ("Retail Cash", NAMEOF('Def market'[Retail Cash]), 0),
    ("Retail Units", NAMEOF('Def market'[Retail Units]), 1)
}

 

 

but i also need to show a table (like below) with multiple columns of calculated field, i.e. YTD growth, MAT growth..etc, is there a way i can use this parameter to quickly switch between the measures? 

 

i.e. formula for calculated LYTD units is like below, i tried write the "choose measure" parameter into the below forumla to replace "sum of retail units" but it doesn't seem to work

 

LYTD Retail =
CALCULATE(SUM('Def market'[Retail Units]),DATESYTD(dateadd('Calendar'[Date],-1,Year),"3/31"))
 

 

 

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi mogugu_84 ,

     

    As far as I know, if you want to use Field parameter function, you need to add all measures you need in it instead of only add the column.

    For example, you need create two measures [LYTD Retail Cash] and [LYTD Retail Units] for LYTD Retails, and the same for others like YTD Retail and FYTD Growth Retail.

    I suggest you to add a calculated column in this parameter table.

    Choose measure = IF(CONTAINSSTRING(Parameter[Parameter],"Cash"),"Retail Cash","Retail Units")

    Then use this column to create a slicer. Result is as below.

     

     

    Best Regards,
    Rico Zhou

     

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

     

     

     

7 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    mogugu_84 

    In the DAX use the paramenter insted of the direct column.

    ex

    LYTD Retail =
    CALCULATE(SUM('ParameterSwitch'[Parameter]),DATESYTD(dateadd('Calendar'[Date],-1,Year),"3/31"))


    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

    • mogugu_84's avatar
      mogugu_84
      Helper I
      1 year ago

      Hi Kedar, sorry, not sure i got this right, i tried copy your formula directly, it doesn't work

      Also tried to replace it with my parameter name "choose measure", also doesn't calculate....

       

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi mogugu_84 , 

     

    In most cases, we use field parameter to choose measures directly to plot a visual. 

     

    But in your case, it seems you expect to use field parameter further in measure definitions, which is a bit more complicated, check:

     

    https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Is-it-possible-to-use-the-selected-Field-Parameter-in-a-DAX/m-p/2533336

     

    https://community.fabric.microsoft.com/t5/Desktop/Referencing-a-Parameter-field-value-in-a-measure/td-p/2957663

     

    • mogugu_84's avatar
      mogugu_84
      Helper I
      1 year ago
       

       Hi, thank you, i tried to follow the example in your first link,

       

      below is the example fields: 

       

      Revenue =
          SWITCH(
              SELECTEDVALUE(Revenue[Revenue Fields]),
              "'Key mesaures'[Revenue with taxes]", [Revenue with taxes],
              "'Key measures'[Revenue without taxes]", [Revenue without taxes]
          ) 

       

      example parameter table looks like this:

      RevenueRevenue FieldsRevenue Order
      Revenue with taxes
      		'Key measures'[Revenue with taxes]0
      Revenue without taxes'Key measures'[Revenue without taxes]1

       

      here's my version, 

       

      Retail =
          SWITCH(
              SELECTEDVALUE('Choose measure'[Choose measure Fields]),
              "'Def market'[Retail Value]", [Retail Value],
              "'Def market'[Retail Volume]", [Retail Volume],
          )

       

      my table of parameter

       

      but the results says "argument "6" is required....

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi mogugu_84 ,

     

    As far as I know, if you want to use Field parameter function, you need to add all measures you need in it instead of only add the column.

    For example, you need create two measures [LYTD Retail Cash] and [LYTD Retail Units] for LYTD Retails, and the same for others like YTD Retail and FYTD Growth Retail.

    I suggest you to add a calculated column in this parameter table.

    Choose measure = IF(CONTAINSSTRING(Parameter[Parameter],"Cash"),"Retail Cash","Retail Units")

    Then use this column to create a slicer. Result is as below.

     

     

    Best Regards,
    Rico Zhou

     

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