Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
singhal14
Helper I
Helper I

Dynamic change in X Axis

‎11-04-2016 06:13 AM

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

 

Is there any way to achieve this. I don't need any alternatives for that like drill down etc. I need that exact thing to be done.

 

If it is possible by dax please suggest something. (I am trying different dax functions to solve this problem but don't know what exact function should be used to do this.)

 

Thanks,

Akash Singhal

Message 1 of 18
89,366 Views
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User
In response to singhal14

‎11-07-2016 08:57 PM

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:
    Capture.PNG
  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]
    Capture2.PNG

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

Message 5 of 18
105,661 Views
17 REPLIES 17

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All