Forum Discussion

singhal14's avatar
singhal14
Helper I
9 years ago
Solved

Dynamic change in X Axis

Hello Everyone,   I am trying a scenario where I need one chart, and it should shows two visuals like sales(YOY) by year and sales(YOY) by area. The x-axis should be change by using Slicer which h...
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Hi singhal14

     

    Here's one idea of how it can be done using a bridging table.

    Could well be other ways of handling this :)

     

    Dummy Power BI model here.

     

    1. Assuming you have Region and Year lookup tables, create a RegionYear table which is the cross product of Region & Year tables.
    2. Duplicate each row of RegionYear and add an Axis Dimension column which is "Region" for half the rows and  "Year" for the other half, and an Axis Value column which is the Region or Year value for each row (depending on the Axis Dimension value).
    3. Relate Year and Region to RegionYear using inactive bidirectional relationships:
    4. Create an Axis Dimension Selected measure to harvest the value of Axis Dimension. Something equivalent to this (this guards against multiple selection):
      Axis Dimension Selected = 
IF (
    ISFILTERED ( RegionYear[Axis Dimension] ),
    IF (
        CALCULATE ( HASONEVALUE ( RegionYear[Axis Dimension] ), ALLSELECTED () ),
        VALUES ( RegionYear[Axis Dimension] )
    )
)
    5. Create a Sales Amount Flexible Axis measure like this (assuming Sales Amount is the normal measure):
      Sales Amount Flexible Axis = 
IF (
    NOT ( ISBLANK ( [Axis Dimension Selected] ) ),
    SWITCH (
        [Axis Dimension Selected],
        "Region",
              CALCULATE (
            [Sales Amount],
            USERELATIONSHIP ( RegionYear[Region], Region[Region] )
        ),
        "Year",
              CALCULATE (
                  [Sales Amount],
                  USERELATIONSHIP ( RegionYear[Year], 'Year'[Year] )
              )
    )
      )
    6. Then you can create visualizations using RegionYear[Axis Value] and [Sales Amount Flexible Axis]

     

harib's avatar
harib
Post Patron
7 years ago

HI OwenAuger 

 

It was nice idea, But when we select any value from Axis Dimension (Like Region), Region name should reflect in table Header instead of displaying the Axis value. can we achive this in Power BI. If yes kindly let me know.

 

Thanks in Advance

 

 

  • OwenAuger's avatar
    OwenAuger
    Super User
    7 years ago

    harib  can you clarify what you mean with a picture or other illustration?

    • harib's avatar
      harib
      Post Patron
      7 years ago

      OwenAuger 

       

      What i mean to say . when we select Region or Year in slicer , it's reflecting in the table, it's fine , But Column title name is showing only Axis value. My requirment is  if we select Region in slicer, Region name also should come on the column title and also same for year, if we select year , year name should reflect on the column title instead of displaying column name (Axis Value)

       

      U have done good job. if you can find the answer of my question that's a great achivement.

       

      Hope u understand.

      • OwenAuger's avatar
        OwenAuger
        Super User
        7 years ago

        Interesting idea harib  :)

         

        I can't find a particularly elegant way to have the column header update based on the Axis Dimension selection.

        The best I can do is create a measure and place it on a card over the top of the original column heading:

        I updated my file to do this - same link here.

         

        Regards,

        Owen