topic Re: Switch measure using parameter in SUM formulas in DAX Commands and Tips

Switch measure using parameter in SUM formulas

mogugu_84 — Wed, 27 Nov 2024 05:09:23 GMT

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"))

Re: Switch measure using parameter in SUM formulas

Kedar_Pande — Wed, 27 Nov 2024 05:37:56 GMT

mogugu_84

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

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

Re: Switch measure using parameter in SUM formulas

mogugu_84 — Wed, 27 Nov 2024 07:16:21 GMT

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

Re: Switch measure using parameter in SUM formulas

FreemanZ — Wed, 27 Nov 2024 07:17:42 GMT

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

Re: Switch measure using parameter in SUM formulas

mogugu_84 — Wed, 27 Nov 2024 07:52:40 GMT

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

Re: Switch measure using parameter in SUM formulas

FreemanZ — Wed, 27 Nov 2024 14:23:44 GMT

hi mogugu_84 ,

try to leave out the last comma.

Re: Switch measure using parameter in SUM formulas

mogugu_84 — Thu, 28 Nov 2024 00:39:08 GMT

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

Re: Switch measure using parameter in SUM formulas

Anonymous — Thu, 28 Nov 2024 06:45:58 GMT

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.