Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
SLJN
Regular Visitor

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

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? 
1 ACCEPTED SOLUTION

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.   

View solution in original post

4 REPLIES 4
v-tangjie-msft
Community Support
Community Support

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. 

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.   

IIPowerBlog
Helper I
Helper I

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!

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. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors