Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure to get Dynamic Period values from Month Column based on Slicer selection Extracted Period

Hi all, 

 

Having Actual table & Master plan table

1.Both tables have 

A. Extracted period column of p8, p9 & p10 data(After appending p8, p9&p10 individual excel files & created extracted period column) 

B. Month Column(available in p8, p9&p10 of both tables) 

 

Wrt to Act table, data is available from fy22 p1-p10 (in Month column) 

 

Wrt to Master Plan table, data is available from fy2019 to fy2023 p1-p12(in Month column) 

 

From Act table, have p1-p10 data

But from Master Plan table, having p1-p12

 

So, when selected from extracted period slicer

Suppose p8 selected,

then in visual it should show data of fy22 p1-p8 from Actual table, 

It should show fy22 p9-p12 from Masterplan table

 

Means dynamically it should show

If P9 selected from extracted period slicer

It should show data of fy22 p1-p9 from Actuals in visual

It should show data of fy22 p10-p12 from Masterplan table

 

How to write this measure where dynamically it should reflect

Kindly suggest a measure

 

Thank you

 

Regards

Krishnakanth

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Best Regards,

    Liu Yang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      Spoiler
      Okay. This is the measure I'm trying to write for getting dynamically. When selected from Extracted period(p10, p9, p8) slicer
      If p10 selected from slicer, wrt Actual(when selected in Slicer) it should show p1-p10 values in matrix visual
      Wrt Masterplan(when selected in slicer) it should show p11-p12 values in matrix visual


       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This is the matrix visual

      Slicer of Extracted Period(FY22P10, FY22P09, FY22P08) 

      Slicer of Type(Actual, MP) 

      So whenever Fy22 p10 clicked & Act(in Type slicer) 

      In visual it should show wrt Actuals p1-p10 data

      Wrt MP p11-p12 values dynamically

      As of now for MP also it is showing p1-p12 as they have given data from p1

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    No, It was not solved

    Again given data with seperate excel files with respect to Masterplan

    Loaded data it has taken

    Problem is now with respect to colour coding for Actuals&MP

    When selected both in slicer

    Only for few rows it is reflecting the colour

     

    Is there any other solution

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     1st measure wrt actual & master plan

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    2nd measure

    Making no colour for Act ytd, MP FC, RFC VS BDG referencing Colour Act&MP measure

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    Sorry for replying to your message only now.

     

    When you say "Only for few rows it is reflecting the colour", does it mean that the corresponding value does not display color after the slicer is selected, or that the value that is not selected by the slicer does not display color.

     

    If it is the second type, you need to add ", "red"" at the end of the IF() function, so that the unselected value is red.

     

    It is recommended that you use the Switch() function to turn two measures into one measure, and list different logical situations line by line. At the end, use a color to display scenes that do not meet various logics, so that there will be colors, when using the IF function for nesting, there will be a logical error.

     

    IF function (DAX) - DAX | Microsoft Docs

    SWITCH function (DAX) - DAX | Microsoft Docs

     

     

    Best Regards,

    Liu Yang

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