Forum Discussion

SLJN's avatar
SLJN
Regular Visitor
3 years ago
Solved

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? 

4 Replies

  • IIPowerBlog's avatar
    IIPowerBlog
    Icon for Helper I rankHelper I
    3 years ago

    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!

    • SLJN's avatar
      SLJN
      Regular Visitor
      3 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.