Dynamic display for date (Years, quarters, months, days) on bar chart

[Smasroor]

I have a dataset that has associate names, commission $, and commission date (mm/dd/yyyy). I have a bar chart that displays commission by year for slected associate (in slicer). I want to provide labels to users for selecting if the want to view the bar graph by year, quarter or month or day. So, basically i want my x axis to dynamically display year, month, quarter or day based on user selection. Coming from Tableau world, I had to create a parameter with year, quarter, month and day options and create a calculated column on top of my date. But, is it doable in Power BI?

[v-juanli-msft]

Hi @Smasroor

Please see the reference:

Dynamic X axis on charts – Power BI

Here is an example file I made with your simple dataset.

[Anonymous]

Hi

1. Create a table ( Home -> Enter Data) with one column with the below values and then create slicer basedon this table.

Options

Year

Quarter

Month

Day

2. Create seperate Measures for YEAR_M , Quarter_M, Month_M, Day_M from commision date. For Ex: Commision Year = YEAR ( MAX(Table1[Commision_Date]))

3. Create a Measeure :

Measure = SWITCH ( TRUE(),
                SELECTEDVALUE( Options_Table[Options]) = "Year" , Your_Table[ Year_M],
                SELECTEDVALUE( Options_Table[Options]) = "Quarter" , Your_Table[ Quarter_M],
                SELECTEDVALUE( Options_Table[Options]) = "Month" , Your_Table[ Month_M],
                SELECTEDVALUE( Options_Table[Options]) = "Day" , Your_Table[ Day_M]
                   )

4. Create chart based on this measure, your column list will be dynamic.

Thanks
Raj

[Smasroor]

Thanks for your response @Anonymous. But looks like i can't use the Switch DAX you mentioned as measure. Below is the error i get:

A single value for column 'monthColumn' in table 'Query1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Thanks and any help is much appreciated.

[Anonymous]

Hi @Smasroor

I have edited my post above, pleasetry again now and let me know if you see any issues.

Thanks

Raj