Slicer Error on Blank Values (Blank and non-blank date)

[MinCK]

Hi folks,

I have trouble filtering multiple values with blank included. For example, filtering rows that dates values are July 2024 and Blank. Selecting July 2024 works fine, however, when I select blank in the slicer, some placeholders rows like 12/31/9999 or 07/31/2202 are included too. 

P.S. the table is connected to dimdate table to get month_year. It previously worked fine if I use date from fact table. however, using date is not user-friendly to scroll down to the specified year and select all 31 dates to cover July

Would love to hear some comments from experts here.

[rajendraongole1 · Accepted solution]

Hi @MinCK - It is likely due to the relationship between your fact table and the dimdate table. Blank values in the slicer from the dimdate table may not directly correspond to blank values in your fact table, leading to unexpected rows like 12/31/9999 or 07/31/2202 being included.

create a measure as likebelow:

Filter Measure =
VAR SelectedDate = SELECTEDVALUE('dimdate'[Month_Year])
RETURN
IF (
SelectedDate = BLANK()
|| SelectedDate = "July 2024",
1,
0
)

Add this measure as a filter in your table visual or slicer with the condition Filter Measure = 1. your dimdate table doesn't have placeholder dates (like 12/31/9999 or 07/31/2202) mapped to blank in the slicer. You can replace these placeholders with actual blank values in Power Query.

In Power Query, identify rows with placeholder dates (12/31/9999, 07/31/2202, etc.).
Replace these values with null or remove them

Table.ReplaceValue(#"Previous Step", #datetime(9999, 12, 31, 0, 0, 0), null, Replacer.ReplaceValue, {"Date"})

If modifying dimdate is not an option, create a calculated column in your fact table to identify rows where the date is either blank or belongs to July 2024.

Custom Filter Column =
IF (
ISBLANK('FactTable'[Date]) || FORMAT('FactTable'[Date], "MMMM YYYY") = "July 2024",
"Include",
"Exclude"
)

try the above approach and let me know.

[v-yajiewan-msft · Accepted solution]

Hi @MinCK , hello rajendraongole1, thank you for your prompt reply!  

Agree with rajendraongole1, I have created a sample for your reference.

Create a measure to exclude dates in the Fact table that are not in the DimDate table:

Filter Dates = IF(MAX('Fact'[Ineffective Date])=BLANK() || MAX('Fact'[Ineffective Date]) IN VALUES(DimDate[Date]),1,0) 

Then, filter the table visual with Filter Dates=1 and select multiple dates to check the result.

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1 · Accepted solution]

Hi @MinCK - It is likely due to the relationship between your fact table and the dimdate table. Blank values in the slicer from the dimdate table may not directly correspond to blank values in your fact table, leading to unexpected rows like 12/31/9999 or 07/31/2202 being included.

create a measure as likebelow:

Filter Measure =
VAR SelectedDate = SELECTEDVALUE('dimdate'[Month_Year])
RETURN
IF (
SelectedDate = BLANK()
|| SelectedDate = "July 2024",
1,
0
)

Add this measure as a filter in your table visual or slicer with the condition Filter Measure = 1. your dimdate table doesn't have placeholder dates (like 12/31/9999 or 07/31/2202) mapped to blank in the slicer. You can replace these placeholders with actual blank values in Power Query.

In Power Query, identify rows with placeholder dates (12/31/9999, 07/31/2202, etc.).
Replace these values with null or remove them

Table.ReplaceValue(#"Previous Step", #datetime(9999, 12, 31, 0, 0, 0), null, Replacer.ReplaceValue, {"Date"})

If modifying dimdate is not an option, create a calculated column in your fact table to identify rows where the date is either blank or belongs to July 2024.

Custom Filter Column =
IF (
ISBLANK('FactTable'[Date]) || FORMAT('FactTable'[Date], "MMMM YYYY") = "July 2024",
"Include",
"Exclude"
)

try the above approach and let me know.