Filtering Issue-Calendar Date(Year) slicer not filtering orders table category wise blank dates

Hi kkanukurthi ,

The issue occurs because selecting "Blank" in the Year slicer removes Orders records where Ship date is blank, as they have no corresponding year in the Calendar table. Since the Calendar[Date] and Orders[Ship date] relationship is one-to-many, filtering the Calendar[Year] column inherently excludes blanks from the Orders table.

A clean solution is to modify the relationship by setting it as inactive and activating it dynamically when needed. First, go to Manage Relationships, locate the relationship between Calendar[Date] and Orders[Ship date], and set it to inactive. Then, update your measure:

Measure_Show_Blank =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
RETURN
IF(
    ISBLANK(SelectedYear),
    CALCULATE(
        COUNTROWS(Orders),
        ALL(Orders[Ship date])  -- Ensures blank values remain visible
    ),
    CALCULATE(
        COUNTROWS(Orders),
        USERELATIONSHIP(Calendar[Date], Orders[Ship date])  -- Applies filtering dynamically
    )
)

This ensures that selecting a specific year correctly filters Orders, while selecting "Blank" in the slicer retains rows with missing dates. If the expected output includes categories with no dates appearing as "Blank," ensure that the Show Items with No Data option is enabled in the matrix visual for Category and Subcategory. This approach keeps the model clean, avoiding unnecessary additional tables or complex workarounds.

Best regards,