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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Lierreyy
Helper I
Helper I

Date Count and Hierarchy Showing Dates That Doesn’t Exist

Hello experts,

I’m finding that the date hierarchy always shows all dates for the year even if most of those dates don’t exist in the column of the dataset. For example, if I have a simple data table called “Products” like this:

 

ProductStart Date
AppleThursday, January 5, 2023
BananaThursday, January 19, 2023
LemonMonday, February 6, 2023

when I make a date slicer with hierarchy, it is showing all dates for the year.

Lierreyy_1-1675696514276.png

I have already turned off “Show items with no data”.

Lierreyy_2-1675696581332.png

In addition to this, if I wanted to find the number of dates using the following formula,

 

Date Count = COUNTROWS(VALUES(Products[Start Date].[Date]))

 

the output is 365, even though I only have 3 unique dates.

Lierreyy_3-1675696666438.png

Do you know why this is happening? How can I get the date hierarchy to display only the dates that actually exist and the date count to count the number of unique dates that actually exists in the table?

Thanks!

4 REPLIES 4
peter_c
New Member

I have this same issue. It's incredibly frustrating that there doesn't seem to be a simple way to resolve it, if there is even any way at all! 

amitchandak
Super User
Super User

@Lierreyy , Join this date with a date table and then use that option

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Hi @amitchandak,

Thanks for the response. I created a Date Table:

Lierreyy_0-1675724913009.png

And I set up the relationship. However, it still shows all quarters and months even though the date table doesn't even have any of those dates.

Lierreyy_1-1675725018231.png

The slicer above is based on the date table field. Is there anything else I need to do to have the slicer only show the quarters, months, and dates that has data? Again, "Show items with no data" is not checked.

Thanks.

I'm having the same problem and I couldn't find a solution. 😞

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.