Forum Discussion

kkanukurthi's avatar
kkanukurthi
Icon for Helper III rankHelper III
1 year ago
Solved

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

Hi All,

I have a year slicer from calendar table. Im not able to filter the blank dates in orders table(category/sub-category). I can filter 2025, 2026 year data but along with this if i select blank on year slicer, i should be able to see category, sub-category columns with blank vaalues in the matrix.

Two table used calender and Orders table:

 

 

Data Modeling--->1-Many

 

1.when 2025 selected, i can view data

2.when Blank slected in slicer, NO Data for category/subcategory values

 

Expected Output/result : 

    

The measure i used for values:

Date_Format_Sign =
var month_year = SELECTEDVALUE('Calendar'[Start of Month])
var mindate = CALCULATE(min(Orders[Ship date]),ALLSELECTED('Calendar'))
var minday = day(mindate)
var mindatestartofmonth = date(year(mindate),month(mindate),1)
var mindatecode = int(year(mindate)&month(mindate))
Return
if(mindatestartofmonth=month_year,
switch (TRUE(),
minday >=1 && minday<7,UNICHAR(9650)&minday,
minday >=7 && minday<14,rept(UNICHAR(160),4)&UNICHAR(9650)&minday,
minday >=14 && minday<=21,rept(UNICHAR(160),8)&UNICHAR(9650)&minday,
REPT(UNICHAR(160), 12)&UNICHAR(9650)&minday))
 
Please help! Thankyou
 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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,

1 Reply

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    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,