Dynamic Barchart X axis by Year slicer

[ranaylor]

Hi All,
I would like to...

1. Replace hard code switch comparison with compare between [Descr Year] and selected value from date slicer
2. Assistance with the existing code as it doesn't work as is. When  I select anything prior to 2025 only several of the bars show up.
Your assistance is appreciated.
I have a report that visualizes Active Employee Demographics. I have a Year tile Slicer connected to a date dimension table. The slicer reports Active employees from 2022 - 2025.

I have a barchart that shows Active Employee by Employee Class. Some of the descriptions of the classes have changed on 1/1/2025. I want the axis to reflect the correct description based on the Year slicer.

I've created a disconnected table Employee Class (dim DynamicEmployee Class)

Employee Class: Portion of Employee Class

Class Descr	Descr Year	Empl Class
EHRA Non-Faculty	2021	ENF
EHRA Non-Faculty	2022	ENF
EHRA Non-Faculty	2023	ENF
EHRA Non-Faculty	2024	ENF
Exempt Professional Staff	2025	ENF
EHRA SAAO Tier 1	2021	ES1
EHRA SAAO Tier 1	2022	ES1
EHRA SAAO Tier 1	2023	ES1
EHRA SAAO Tier 1	2024	ES1
Senior Admin Academic Officers	2025	ES1
EHRA SAAO Tier 2	2021	ES2
EHRA SAAO Tier 2	2022	ES2
EHRA SAAO Tier 2	2023	ES2
Exempt Professional Staff ES2	2024	ES2

The Dax I've been working with so far is hardcoded to the descr Year column to see if this works: I would like to replace the hard coded years 2025,2024... with a comparison between [Descr Year] and the selected value in the years slicer

tstDynamicEmployeeClass2 = 
    if(HASONEVALUE('dim DynamicEmployee Class'[Descr Year]),
        SWITCH(VALUES('dim DynamicEmployee Class'[Descr Year])
            ,2025, 
            CALCULATE([Active Employee]
                    ,TREATAS(VALUES('dim DynamicEmployee Class'[Class Descr])
                            ,tstEmployeeClass[Employee Class]))
            ,2024, CALCULATE([Active Employee]
                    ,TREATAS(VALUES('dim DynamicEmployee Class'[Class Descr])
                            ,tstEmployeeClass[Employee Class]))
            ,2023, CALCULATE([Active Employee]
                    ,TREATAS(VALUES('dim DynamicEmployee Class'[Class Descr])
                            ,tstEmployeeClass[Employee Class]))
            ,2022, CALCULATE([Active Employee]
                    ,TREATAS(VALUES('dim DynamicEmployee Class'[Class Descr])
                            ,tstEmployeeClass[Employee Class]))                       
            ,2021, CALCULATE([Active Employee]
                    ,TREATAS(VALUES('dim DynamicEmployee Class'[Class Descr])
                            ,tstEmployeeClass[Employee Class]))
        )
    )

Thanks,
Andy...

[Anonymous]

Hi @ranaylor 

Maybe you can try this:

Here’s a revised version of your DAX measure that compares `[Descr Year]` with the selected value from the year slicer:

tstDynamicEmployeeClass2 =
VAR SelectedYear =
    SELECTEDVALUE ( 'DateTable'[Year] )
RETURN
    IF (
        HASONEVALUE ( 'dim DynamicEmployee Class'[Descr Year] ),
        CALCULATE (
            [Active Employee],
            FILTER (
                'dim DynamicEmployee Class',
                'dim DynamicEmployee Class'[Descr Year] = SelectedYear
            ),
            TREATAS (
                VALUES ( 'dim DynamicEmployee Class'[Class Descr] ),
                tstEmployeeClass[Employee Class]
            )
        )
    )

Give this a try and see if it resolves the issue with the bars not showing up for the years prior to 2025. Let me know if you need further assistance!

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ranaylor]

Results after selecting current [Descr Year]