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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
datacode
Frequent Visitor

Slicer Showing All dates in calendar Dropdown Rather Than Dates with Data Only

Hi have 2 tables:

1. Calendar: has daily dates for 4 years

2. Fact table: Sales of weekly data

Relationship is on to many.

When I add Date from the calendar table in to a slicer to filter for specific dates, the dropdown shows all dates from the calendar date rathern than only dates where they both have data in common.

 

Is there any way to get around this other than using the Date column from the Fact table (sales table)?

 

datacode_1-1684523598307.png

datacode_3-1684523618014.png

 

datacode_2-1684523612920.png

 

datacode_4-1684523659030.png

 

 

1 ACCEPTED SOLUTION
Mahesh0016
Super User
Super User

@datacode Please refer below Snap

SameDate =
IF(
    CALCULATE(
                COUNT('Table'[Invoice Date]),
                TREATAS(
                        VALUES('Table'[Invoice Date]),
                        'Date'[Date]
                       )
            )>0,1,0
)

Mahesh0016_0-1684570843888.png

Mahesh0016_1-1684570894799.png

 

@datacode THANK YOU!!

View solution in original post

3 REPLIES 3
Mahesh0016
Super User
Super User

@datacode Please refer below Snap

SameDate =
IF(
    CALCULATE(
                COUNT('Table'[Invoice Date]),
                TREATAS(
                        VALUES('Table'[Invoice Date]),
                        'Date'[Date]
                       )
            )>0,1,0
)

Mahesh0016_0-1684570843888.png

Mahesh0016_1-1684570894799.png

 

@datacode THANK YOU!!

LuizKoller
Resolver I
Resolver I

IMO, the best solution for this is set the direction filter for both:

LuizKoller_0-1684528356565.png



There are other solutions, like lateral filters, where "Units" > 0.

 

Hi @LuizKoller Thank you for your response. I like your suggestion of changing the directional filter, but it does not appear to work on my side. Would there be any other changes I can make?

datacode_0-1684770046087.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.