How to have slicer change graph Y-axis when selection is made?

[dcheng029]

Hi all,

I am working with two datasets where [A] shows a list of all items with the relevant staff and business area and [B] shows a list of all staff and their relevant department and executive. [B] has a one-to-many relationship with [A] using the staff columns.

On my dashboard, I have an Executive filter from [B]. I want my column graph to show the Department from [B] as the Y-axis when no selection is made on the Executive filter and Business Area from [A] as the Y-axis when a selection is made on the Executive filter.

Due to the complexity and sensitivity of my dataset I have included the below summary instead; hoping anyone can help here.

[danextian]

Hi @dcheng029 

Create a disconnected table that combines both department and business are columns into one.

BusinessArea_Dept = 
VAR _BA =
    SELECTCOLUMNS ( DatasetA, "Category", DatasetA[Business Area] )
VAR _dept =
    SELECTCOLUMNS ( DatasetB, "Category", DatasetB[Department] )
RETURN
    DISTINCT ( UNION ( _BA, _dept ) )

Create this measure

Row Count = 
COUNTROWS ( DatasetA )
---can be any other aggregation

Switch Measure = 
VAR _BA =
    CALCULATE (
        [Row Count],
        TREATAS ( VALUES ( BusinessArea_Dept[Category] ), DatasetA[Business Area] )
    )
VAR _Dept =
    CALCULATE (
        [Row Count],
        TREATAS ( VALUES ( BusinessArea_Dept[Category] ), DatasetB[Department] )
    )
RETURN
    IF ( ISFILTERED ( DatasetB[Executive] ), _BA, _Dept )

Please see the attached pbix

[ryan_mayu · Accepted solution]

@dcheng029 

pls see the attachment below

[dcheng029]

Hi @ryan_mayu 

Many thanks! This worked a charm and was exactly what I was after. I have incorporated your logic to my actual dataset and it translated perfectly. Cheers!

[ryan_mayu]

you are welcome

[Shahid12523]

You can do this with DAX + dynamic axis:

Create a measure to check if Executive is selected:

IsExecutiveSelected =
IF(ISFILTERED(TableB[Executive]), "Business Area", "Department")

Create a measure for axis value:

Dynamic Axis =
SWITCH(
[IsExecutiveSelected],
"Business Area", SELECTEDVALUE(TableA[Business Area]),
"Department", SELECTEDVALUE(TableB[Department])
)

Create a measure for count:

Dynamic Count =
SWITCH(
[IsExecutiveSelected],
"Business Area", COUNTROWS(TableA),
"Department", COUNTROWS(TableB)
)

Use Dynamic Axis on chart axis and Dynamic Count as value.

Chart will switch between Department and Business Area automatically based on Executive slicer.

[danextian]

This only switches the table which rows are to be counted but not what to show in the X/Y axis.