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]

     

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
    6 years ago

    I am trying to implement same approach, however try to avoid dupliation of the table rows to create the "Bridge Table" due to large amount of data. Wondering if any way to achive this without duplicate table? - "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)."

    One approach tried is to use a report level @Parameter called Para_XAxis for the switch function, however though the Parameter's value is updated smoothly and readable in card, the switch function just failed to get a valid value from the @Parameter. Wondering if anything I missed? 

    Sales Amount Flexible Axis = 
IF (
    NOT ( ISBLANK ( Para_XAxis[Parameter Value] ) ),
    SWITCH (
        Para_XAxis[Parameter Value],
        "Region",
            CALCULATE (
            [Sales Amount],
            USERELATIONSHIP ( RegionYear[Region], Region[Region] )
        ),
        "Year",
            CALCULATE (
                [Sales Amount],
                USERELATIONSHIP ( RegionYear[Year], 'Year'[Year] )
            )
    )
    )

     

    • harib's avatar
      harib
      Post Patron
      6 years ago

      Anonymous 

       

      Thanks for your responce and solution. If u have any sample file can u please upload .

       

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I followed the steps, but i am facing sum of region or month. i am getting sum of whole column for every region or month. can you please help me to resolve the issue.

     

     

    Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi OwenAuger ,

     

    Thanks for commenting,

     

    here the are slicers to change the visual, if i use this then again i need to use the slicer to change the X axis and also i need to provide the orginal slicer which is Region to selected specific region and Year to select specifi year.

     

    The partner want to see the year(Changing for region X axis) when they select any region in region filter itself. They dont want seprate slicer to change the X axis.

     

    regards,

    Nagesh

    • OwenAuger's avatar
      OwenAuger
      Super User
      5 years ago

      Hi Anonymous 

       

      You could do something like this, with a disconnected Region/Year dimension that contains values from both Region & Year. This dimension is used on the axis of your visual(s).

      Then you can create a measure that detects whether Region is filtered.

       

      Here is an example I set up (PBIX link).

      I have a Sales table with columns Region, Year & Sales.

       

      RegionYear (disconnected table) looks like this:

       

      Then write a measure like this:

      Sales for Chart = 
IF ( 
    ISFILTERED ( Sales[Region] ),
    CALCULATE ( 
        SUM ( Sales[Sales] ),
        TREATAS (
            VALUES ( RegionYear[Year] ),
            Sales[Year]
        )
    ),
    CALCULATE ( 
        SUM ( Sales[Sales] ),
        TREATAS ( 
            VALUES ( RegionYear[Region] ),
            Sales[Region]
        )
    )
)

       Then the visuals look like this:

       

       

       

      Regards,

      Owen