cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver III

Switching Calculation with Field Parameters in Power BI Visuals

Hey Power BI community,

I've noticed that since 2022, there's an option in Power BI to switch between calculations using field parameters. However, I'm currently working on a visual where I want to sum up the relevant calculation after selecting a parameter. Is this possible, or is there a workaround for this scenario?

Greets,

Ronald

1 ACCEPTED SOLUTION
Super User

Hi @Ronald123 ,

Redo the measure to:

``````Total Sum =
VAR _a =
SELECTCOLUMNS ( Parameter, "Name",[Parameter])
RETURN
SUMX (
_a,
SWITCH (
[Name],
"APPEL", [APPEL],
"ORANGE", [ORANGE],
"BANANA", [BANANA],
0
)
)
``````

I added a new row Banana to get some different results but it's working has needed:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

8 REPLIES 8
Resolver III

When I use the formula now (the 1st option), there is no total when I select 2 parameters at the same time. Can you help me modify the formula so that it adds Calculation A and Calculation B together?

Best regards,

Ronald

Super User

Hi @Ronald123 ,

To what I can understand you want to be abble to make the sum of the selected measure based on the field parameter?

So for example if you selecte Measure A and the values are 1 , 2, 3 you get 6 if you selecte measure B and the values are 4, 5, 6 you get 15. Is this correct?

In this case you i believe you have two options one is two create a measure that uses a switch function changing the context of your calculation it would be something similar to this:

``````Total Sum = SUMX (
VALUES ( Table[Column] ),
"ValuesTotal",
SWITCH (
SELECTEDVALUE ( Parameter[Parameter Order] ),
0, [Measure A],
1, [Measure B],
2, [Measure C]
)
),
[ValuesTotal]
)``````

Another option that you have is to have a calculation group created using tabular editor that way the measure would be different:

``````SUMX (
VALUES ( Table[Column] ),
"ValuesTotal", SELECTEDMEASURE ()
),
[ValuesTotal]
)``````

Check this link for the Calculation groups option:

https://www.sqlbi.com/articles/introducing-calculation-groups/

https://www.sqlbi.com/calculation-groups/

The combination of calculation items with field parameters is very powerfull.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Resolver III

Resolver III

Hello Miguel Félix,

When I use the formula now (the 1st option), there is no total when I select 2 parameters at the same time. Can you help me modify the formula so that it adds Calculation A and Calculation B together?

Best regards,

Ronald

Resolver III

Hello @MFelix ,

To complement my question, I am hereby sending you my test file.

Greets,

Ronald

Super User

Hi @Ronald123 ,

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Resolver III

Hi @MFelix ,

The test file can now be accessed without a password.

Greets,

Ronald

Super User

Hi @Ronald123 ,

Redo the measure to:

``````Total Sum =
VAR _a =
SELECTCOLUMNS ( Parameter, "Name",[Parameter])
RETURN
SUMX (
_a,
SWITCH (
[Name],
"APPEL", [APPEL],
"ORANGE", [ORANGE],
"BANANA", [BANANA],
0
)
)
``````

I added a new row Banana to get some different results but it's working has needed:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.