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]

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi singhal14,

 

>>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 have two values Year and Area

 

According to your description, you want to use slicer to choose which column displayed on the visual as the axis,right?

Based on test, currently power bi desktop visuals not support this feature.

 

For your requirement, you can post it to idea.

https://ideas.powerbi.com/forums/265200-power-bi

 

Regards,

Xiaoxin Sheng

  • singhal14's avatar
    singhal14
    Helper I
    9 years ago

    Hi Anonymous,

     

    Thanks for Reaching out.

     

    I am aware about that power bi does not support this feature. 

    But if we try it with using DAX funtions, may be we can achieve it.

    I need your suggestions can we do it by using DAX Functions and if the answer is YES then what would be the approach?

     

    Thanks,

    Akash Singhal

    • OwenAuger's avatar
      OwenAuger
      Super User
      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

        OwenAuger 

         

        It's really great,but in my senario If we dont select any value from slicer(Axis Dimension) can we get aggregated sale amount value (4040) . If it's possible kindly let me know. 

         

        Thanks in advance

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi singhal14,

       

      You can use dax to write a measure which can work with the slicer, but you can't use dax to create a dynamic column.(measure can't drag to axis field)

       

      Sample:

      Measure to get the slicer value:

      Selected Value = if(HASONEVALUE('Table 2'[Type]),VALUES('Table 2'[Type]),BLANK())

       

      Calculate column:

       

      Measure:

       

       

      Regards,

      Xiaoxin Sheng