Forum Discussion

mogugu_84's avatar
mogugu_84
Icon for Helper I rankHelper 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 ...
  • 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.

     

     

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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....

     

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      1 year ago

      hi mogugu_84 ,

       

      try to leave out the last comma.

      • mogugu_84's avatar
        mogugu_84
        Icon for Helper I rankHelper I
        1 year ago

        Thanks again Freeman, yes, that makes the formula tick, but sorry, the whole method still just doesn't work...

         

        here's one of the formula for growth calculation 

         

        LYTD Retail =
        CALCULATE([Retail Volume],DATESYTD(dateadd('Calendar'[Date],-1,Year),"3/31"))
         
        Here's the switch formula
        Retail =
            SWITCH(
                SELECTEDVALUE('Test Parameter'[Test Parameter Fields]),
                "'Def market'[Retail Value]", [Retail Value],
                "'Def market'[Retail Volume]", [Retail Volume]
            )
        Parameter table:
         
         
         
        but finally, the numbers don't change when i use this parameter......not sure where it went wrong