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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aAmirkHan
Helper I
Helper I

Blank year in slicer

Hi,

I am getting blank in my slicer. How I can fix it? Instead of hiding it in slicer pane. is there any other solution to remove this blan?
Thank you

 

I have joined calendar date table with order date table in data modeling. Order date is starting from 04th July,  1996 to 06th May 1998. However shipping date is till 11th June, 1998. There is no blank in order date table,

 

Year is coming from Dim table.

Net Sales is a Dax on fact table.

 

aAmirkHan_0-1677043742209.png

 

 

aAmirkHan_1-1677043759983.png

 

 

aAmirkHan_2-1677043793999.pngaAmirkHan_3-1677043813568.png

 

aAmirkHan_4-1677044435043.png

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @aAmirkHan ,

 

from the screenshot of the data model, it becomes obvious that there are three relationships from the date table to the orders table.

Even if some of the relationships are inactive (not active) and the column that "feeds" this slicer does not contain missing or blank values, these relationships are responsible that a blank value "bubbles up" to the slicer.

 

The "(blank)" appears in a slicer, when a value inside of the column on the many-side of a relationship is blank or the value is not present on the one-side of the relationship.

 

From a technical point of view, "missing values" in the dimension table () are raising a referential integrity violation. RI violations can degrade performance: Clean data = faster reports - Phil Seamark on DAX 
For this reason, you have to fill in the missing values.

For a date dimension this is not that easy, as non-date values like "not applicable" or "not available yet" can not be entered. If a date value is missing I use the latest available date from the calendar table but also add a new column like "billing date type" (I add this column for each date column) to the table. When there is a billing date, the value becomes "fact" otherwise "projected", this allows separating the dates. Next to that, some calculations become faster as it's not necessary to iterate through a table and check if a value is empty. Of course, you have to make sure that the columns are properly updated whenever your data gets refreshed and the range of the date table is changing.

 

Hopefully, this helps to tackle your challenge.

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey @aAmirkHan ,

 

it's also.a good practice to start the calendar table on the 1st of January and end the calendar table on the 31st of December.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

Hey @aAmirkHan ,

 

from the screenshot of the data model, it becomes obvious that there are three relationships from the date table to the orders table.

Even if some of the relationships are inactive (not active) and the column that "feeds" this slicer does not contain missing or blank values, these relationships are responsible that a blank value "bubbles up" to the slicer.

 

The "(blank)" appears in a slicer, when a value inside of the column on the many-side of a relationship is blank or the value is not present on the one-side of the relationship.

 

From a technical point of view, "missing values" in the dimension table () are raising a referential integrity violation. RI violations can degrade performance: Clean data = faster reports - Phil Seamark on DAX 
For this reason, you have to fill in the missing values.

For a date dimension this is not that easy, as non-date values like "not applicable" or "not available yet" can not be entered. If a date value is missing I use the latest available date from the calendar table but also add a new column like "billing date type" (I add this column for each date column) to the table. When there is a billing date, the value becomes "fact" otherwise "projected", this allows separating the dates. Next to that, some calculations become faster as it's not necessary to iterate through a table and check if a value is empty. Of course, you have to make sure that the columns are properly updated whenever your data gets refreshed and the range of the date table is changing.

 

Hopefully, this helps to tackle your challenge.

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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