Using parameters within measures

[Pradgouda18]

Here is a detailed description of my problem.

I have financial information in multiple columns - 2024Actual, 2024 Budget, 2023 Actual, 2023 Budget, 2022 Actual, Midyearforecast1, Midyearforecast2 etc. Let us call these datasets. Typically I look for comparing any two datasets. For example 2024Actual and 2023Actual. 

There are other columns with rows either mentioning the month, quarter, type of information (Revenue or expense, type of expenses or production), plant name, manager name etc.

Once I choose the two datasets, the analysis is mostly related to showing the variances. If I know which two datasets I am comparing, I can create measures that tell Revenue, EBITDA, difference between the two and create visualizations. No problem with that. But I don't know which datasets I will analyze and compare ahead of time

I want me or another user to be able to choose the two datasets either through field parameters or some other way (I don't if there is any other way) in Power BI interface. Lets call them dataset1 and dataset2. I want to create measures the calculate the same Revenue, EBITDA, differences between the values in dataset1 and dataset2 but I am not able to create any measures based on the field parameter.

I tried following different options but nothing works. Not getting any syntax error but visualization shows blank

1. Switching between Parameters using SWITCH

Revenue1 =

SWITCH ( selectedvalue(Parameter[Parameter Fields]),

"2024Actual",[2024ActualRevenue],

"2024Budget",[2024BudgetRevenue],

Blank()

)

2024ActualRevenue and 2024BudgetRevenue are both measures. Trying to choose between measures using the 'Parameter'. They work fine if I use them in visualizations but when I use 'Revenue1' measure, i am getting blanks in the visualizations

2024ActualRevenue =

CALCULATE(SUM('Data'[2024Actual]), 'Data'[Cost Category] IN { "Revenue" })

2. Using SWITCH to create measure

Revenue2 =

SWITCH (Selectedvalue(Selected2[Parameter Fields]),

"2024Actual",calculate(sum(Data[2024Actual]),Data[Cost Category] IN {"Revenue"}),

"2024Budget",calculate(sum(Data[2024Budget]),Data[Cost Category] IN {"Revenue"}),

"2023Actual",calculate(sum(Data[2023Actual]),Data[Cost Category] IN {"Revenue"}),

Blank()

)

Same problem. Not getting any error with syntax but visualizations are blank. Data is the name of the tabe, Cost category differentiates type of data - Revenue, production, expenses. 

Appreciate any help.

[Pradgouda18 · Accepted solution]

Instead of using Selectedvalue(Selected2[Parameter Fields]), I used Selectedvalue(Selected2[Parameter Order]) and used the sort order. It worked.

[v-tianyich-msft]

Hi @Pradgouda18 ，

In case you are using switch, the first parameter should be added true()

Revenue1 =
SWITCH ( true(),
selectedvalue(Parameter[Parameter Fields]),
"2024Actual",[2024ActualRevenue],
"2024Budget",[2024BudgetRevenue],
Blank()
)

Revenue2 =
SWITCH (true(),
Selectedvalue(Selected2[Parameter Fields]),
"2024Actual",calculate(sum(Data[2024Actual]),Data[Cost Category] IN {"Revenue"}),
"2024Budget",calculate(sum(Data[2024Budget]),Data[Cost Category] IN {"Revenue"}),
"2023Actual",calculate(sum(Data[2023Actual]),Data[Cost Category] IN {"Revenue"}),
Blank()
)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Pradgouda18]

Thanks. I tried. This is not working