Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |