topic Re: Return two values from two different measures, based on one selected column. in DAX Commands and Tips

Return two values from two different measures, based on one selected column.

SLJN — Wed, 14 Dec 2022 12:23:34 GMT

Hi,

I'm trying to return planned weight and actual weight for the last 30 days based on a selected BU.

When the user select bu ANR in a slicer and then rolling 30 days in another I want them to display both actual weight and planned weight by just choosing "rolling 30".

Background:

There are several business units, and they have several products, but its only certain products and their weight that I'm interested of.

Firstly, I have created a measure for each unit and their products and weights.

Actial Production (SOUTH) =

CALCULATE (

[ACTUAL WEIGHT(KG)],

KEEPFILTERS(DIM_BU[Buid] = "SOUTH"),

KEEPFILTERS(DIM_PRODUCT[PRODUCT] IN {"Banana","Apple","Pear"})

)

Planned production (SOUTH) =

CALCULATE (

[PLANNED WEIGHT(KG)],

KEEPFILTERS(DIM_BU[Buid] = "SOUTH"),

KEEPFILTERS(DIM_PRODUCT[PRODUCT] IN {"Banana","Apple","Pear"})

)

Then I have created a SWITCH

Production Switch =

VAR BU = SELECTEDVALUE((DIM_BU[Buid]])

Return

SWITCH(

True(),

BU= "SOUTH", [Production (SOUTH)],

...)

Planned Production Switch =

VAR BU = SELECTEDVALUE((DIM_BU[Buid]])

Return

SWITCH(

True(),

BU= "SOUTH", [Production (SOUTH)],

...)

I have done a measure for rolling 30, ytd etc.

In my report I have a slicer for the BU but i also want a slicer that only displays rolling 30 days, month, ytd. And when the user select rolling 30 a line chart will appear with two lines, that is actual and planned production.

How is the easiest way to solve this?

Re: Return two values from two different measures, based on one selected column.

IIPowerBlog — Thu, 15 Dec 2022 08:21:41 GMT

hello SLJN . I would create a calculated column with an if clause (get the 'if rolling 30 days' syntax like you have done already) and if true set it to 1 else 0. So you will have on a row level dynamically this information, whether this lineitem belongs to rolling 30 days or not. Then you can work on the 1s and 0s to create a slicer or a button. Does this make sense? thanks!

Re: Return two values from two different measures, based on one selected column.

Anonymous — Thu, 15 Dec 2022 09:01:30 GMT

Hi SLJN ,

According to your requirements description, what you want to achieve is to display the displayed fields in your line chart according to the user's choice of the slicer on the report page according to the user's selection, such as when the user chooses to scroll 30, a line chart with two lines will appear, that is, actual production and planned production, for this requirement I think the best solution at present is to try to use Power BI's new preview function field parameter in recent months, It can support the display of specific visuals in your report according to the selection in the field slicer, you can refer to this official document to explore your solution:

Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

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

Re: Return two values from two different measures, based on one selected column.

SLJN — Thu, 15 Dec 2022 17:58:18 GMT

Hi,

I found this with field parameters Using SELECTEDVALUE with Fields Parameters in Power BI - SQLBI. I would need to use summarize and that's not optimal.

I created a table instead, together with a switch and now it works.

Re: Return two values from two different measures, based on one selected column.

SLJN — Thu, 15 Dec 2022 18:01:51 GMT

Hi,

I wanted a more dynamic way of doing it so i created a table with only one column and used abbreviaation for every average. Then i used switch to connect it.