Switch measure using parameter in SUM formulas

[mogugu_84]

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 · Accepted solution]

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]

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

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

[mogugu_84]

 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:

Revenue	Revenue Fields	Revenue 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]

hi @mogugu_84 ,

try to leave out the last comma.

[mogugu_84]

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

[Kedar_Pande]

@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]

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