Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Chibi-devil
New Member

Could not add multiple data in Slicer's Data Field

Hi,

Today I encountered a problem that I could not add Year and Month data in Data Field for Slicer.

It looks awkward when I selected date and tried to add both Year and Month, Month Field is moved to "Hint" field and I could not make it dropdown list.

I've confirmed data type is set as "Date" correctly of the data.

Will someone help me how to fix this problem?

----

I always use Slicer for Date to make it show graphs by filtering based upon Year/Month.

Usually when I tried to add Date (Date Hierarchy) into field, I could add Year and Month.

----

 

PowerBI Desk Top version 2.126.1261.0 64-bit 

1 ACCEPTED SOLUTION
AnalyticsWizard
Solution Supplier
Solution Supplier

@Chibi-devil 

 

Hi there!

It sounds like you're facing a little hiccup with the slicer functionality in Power BI Desktop. Normally, when you add a date to a slicer, Power BI automatically gives you a hierarchy that lets you drill down from year to quarter to month, and so on. But it seems like that's not happening this time around. No worries though, we can try a couple of steps to troubleshoot this.

First off, let's make sure we're not missing something simple:

 

1. Hierarchy Slicer: When you drag your date field into the slicer, make sure to select the arrow next to the field to expand and see the hierarchy (Year > Quarter > Month > Day). Sometimes, it defaults to a single level.

 

2. Clear Slicer Selections: If there's any existing selection or filter within the slicer, try clearing it. This might reset any issues and allow you to add both Year and Month again.

 

If the basics look fine, let's try a more manual approach:

 

1. Create Custom Columns: In Power BI Desktop, you can create custom columns for the Year and Month. Here's how:
- Go to the "Transform Data" section by clicking "Transform Data" from the Home tab.
- In the Power Query Editor, select the date column, then under the "Add Column" tab, use the "Date" dropdown to add a "Year" and "Month" column.
- Once you've added these, go back to the report view, and you should be able to add these new columns to the slicer individually.

 

2. Create a Date Table: Sometimes, it helps to have a separate date table with unique Year and Month columns. You can create one in Power BI using DAX:
- Go to the Modeling tab and choose "New Table".
- Use the DAX formula to create a calendar table. Here’s a simple example:
```
DateTable = CALENDAR (DATE(2000,1,1), DATE(2025,12,31))
```
- Then add Year and Month columns to this new table.
- Define relationships between this date table and your data, and use the Year and Month columns from the date table in your slicer.

 

3. Check for Updates: Ensure your Power BI Desktop is up to date. Sometimes bugs are fixed in newer releases.

If after trying these steps the slicer still isn't behaving as expected, it could be a more unique issue related to the latest update or something specific in your Power BI file.

 

And of course, if you feel like you're going in circles, feel free to give a shout. Sometimes two heads are better than one, especially when it comes to puzzling out tech gremlins. Let's get that slicer sorted so you can get back to the fun part—digging into your data!

View solution in original post

7 REPLIES 7
AnalyticsWizard
Solution Supplier
Solution Supplier

@Chibi-devil 

 

Hi there!

It sounds like you're facing a little hiccup with the slicer functionality in Power BI Desktop. Normally, when you add a date to a slicer, Power BI automatically gives you a hierarchy that lets you drill down from year to quarter to month, and so on. But it seems like that's not happening this time around. No worries though, we can try a couple of steps to troubleshoot this.

First off, let's make sure we're not missing something simple:

 

1. Hierarchy Slicer: When you drag your date field into the slicer, make sure to select the arrow next to the field to expand and see the hierarchy (Year > Quarter > Month > Day). Sometimes, it defaults to a single level.

 

2. Clear Slicer Selections: If there's any existing selection or filter within the slicer, try clearing it. This might reset any issues and allow you to add both Year and Month again.

 

If the basics look fine, let's try a more manual approach:

 

1. Create Custom Columns: In Power BI Desktop, you can create custom columns for the Year and Month. Here's how:
- Go to the "Transform Data" section by clicking "Transform Data" from the Home tab.
- In the Power Query Editor, select the date column, then under the "Add Column" tab, use the "Date" dropdown to add a "Year" and "Month" column.
- Once you've added these, go back to the report view, and you should be able to add these new columns to the slicer individually.

 

2. Create a Date Table: Sometimes, it helps to have a separate date table with unique Year and Month columns. You can create one in Power BI using DAX:
- Go to the Modeling tab and choose "New Table".
- Use the DAX formula to create a calendar table. Here’s a simple example:
```
DateTable = CALENDAR (DATE(2000,1,1), DATE(2025,12,31))
```
- Then add Year and Month columns to this new table.
- Define relationships between this date table and your data, and use the Year and Month columns from the date table in your slicer.

 

3. Check for Updates: Ensure your Power BI Desktop is up to date. Sometimes bugs are fixed in newer releases.

If after trying these steps the slicer still isn't behaving as expected, it could be a more unique issue related to the latest update or something specific in your Power BI file.

 

And of course, if you feel like you're going in circles, feel free to give a shout. Sometimes two heads are better than one, especially when it comes to puzzling out tech gremlins. Let's get that slicer sorted so you can get back to the fun part—digging into your data!

Thank you, Analytics Wizard!

Yeh, it seemed I was trapped by PowerBI hiccup lol. When I cleared all slicer setting and started from the beginning, I've got date slicer as I expected.

THANK YOU!!

Ritaf1983
Super User
Super User

Hi @Chibi-devil 
If I understood you correctly you are trying to use the "date" column as a date hierarchy at Slicer. 
Then you need to activate the option of "Auto date-time" :

Ritaf1983_0-1712943781529.pngRitaf1983_1-1712943826344.png

 

+ Switch on the option of date hierarchy when you put it on slicer :

Ritaf1983_2-1712944026733.png

pbix with the example is attached

Hi,

Thank you for trying to help me out. I've confirmed "Auto-date time" has been already turned on in both global setting and current file.

In addition, I added Date Hierarchy into the Slicer data field but it does not work as attached image (Sorry menu displays in Japanese...).

Need your further help how to fix this... awkward_Date_Field.png

Hi @Chibi-devil,

 

I just checked it by dummy testing, it looks like you are using style as tile and it appears that when you keep more than 1 fields, you can only use two styles, vertical list and drop down. If you keep just one field in slicer, you get more style options. Suggest you to play with this finding and see if it helps you.

Hi @Chibi-devil ,

 

Could you share the sample .pbix file? Since its slicer related issue, perhaps you can just keep that date table and rename it to english and make sure probelm exist in this sample version.

Hi, I've already resolved my problem with Analytics Wizard. So, no further support is needed.

Thank you for the kind help 🙂

Have a wonderful day ahead.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.