cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
ASuhaji
Frequent Visitor

Column chart: Parameter to X-axis with date hierarchy

Hello Everyone,

 

I need your help with the following.

 

I want to create a dynamic slicer similar to the one shown in this video.

 

My problem is that one of the fields is a date column with date hierarchy. Currently, this is the related DAX code:

X-Axis = {
    ("Date", NAMEOF('Data'[Date])), 0),
    ("Supervisor", NAMEOF('Data'[Supervisor]), 1),
    ("Controller", NAMEOF('Data'[Controller]), 2)
}
 
The dynamic x-axis works perfectly with this too, the problem is that if I choose the date option, I get a breakdown by day and not the originally planned date hierarchy, where I can use the drill function.

 

Do you know of any way to solve it?

 

Thank you in advance.

2 ACCEPTED SOLUTIONS
ASuhaji
Frequent Visitor

I found a good solution for it.

 

This is how I changed the code of the parameter:

 

X-Axis = {
    ("Year", NAMEOF('Data'[Date].[Year]), 0),
    ("Quarter", NAMEOF('Data'[Date].[Quarter]), 1),
    ("Month", NAMEOF('Data'[Date].[Month]), 2),
    ("Day", NAMEOF('Data'[Date].[Day]), 3),
    ("Supervisor", NAMEOF('Data'[Supervisor]), 4),
    ("Regional Controller", NAMEOF('Data'[Controller]), 5)
}
 
After that I created 3 bookmarks which first state is the selected year, quarter, month, day fields, the second is only the supervisor, the third is the controller. Then I hid the related slicer and use only the bookmark navigator.

View solution in original post

ASuhaji
Frequent Visitor

I found a much simpler and more elegant solution:

 

X-Axis = {
    ("Year", NAMEOF('Data'[Date].[Year]), 0, "Date"),
    ("Quarter", NAMEOF('Data'[Date].[Quarter]), 1, "Date"),
    ("Month", NAMEOF('Data'[Date].[Month]), 2, "Date"),
    ("Day", NAMEOF('Data'[Date].[Day]), 3, "Date"),
    ("Supervisor", NAMEOF('Data'[Supervisor]), 4, "Supervisor"),
    ("Regional Controller", NAMEOF('Data'[Controller]), 5, "Regional Controller")
}
 
And I use the 4th column for the slicer.

View solution in original post

2 REPLIES 2
ASuhaji
Frequent Visitor

I found a much simpler and more elegant solution:

 

X-Axis = {
    ("Year", NAMEOF('Data'[Date].[Year]), 0, "Date"),
    ("Quarter", NAMEOF('Data'[Date].[Quarter]), 1, "Date"),
    ("Month", NAMEOF('Data'[Date].[Month]), 2, "Date"),
    ("Day", NAMEOF('Data'[Date].[Day]), 3, "Date"),
    ("Supervisor", NAMEOF('Data'[Supervisor]), 4, "Supervisor"),
    ("Regional Controller", NAMEOF('Data'[Controller]), 5, "Regional Controller")
}
 
And I use the 4th column for the slicer.
ASuhaji
Frequent Visitor

I found a good solution for it.

 

This is how I changed the code of the parameter:

 

X-Axis = {
    ("Year", NAMEOF('Data'[Date].[Year]), 0),
    ("Quarter", NAMEOF('Data'[Date].[Quarter]), 1),
    ("Month", NAMEOF('Data'[Date].[Month]), 2),
    ("Day", NAMEOF('Data'[Date].[Day]), 3),
    ("Supervisor", NAMEOF('Data'[Supervisor]), 4),
    ("Regional Controller", NAMEOF('Data'[Controller]), 5)
}
 
After that I created 3 bookmarks which first state is the selected year, quarter, month, day fields, the second is only the supervisor, the third is the controller. Then I hid the related slicer and use only the bookmark navigator.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors