Exclude dates from Date Table which are not available in the fact table

Question

I have a between slicer which gets the dates from my Date Table (Table contains earlier dates that the data from the Satisfaction table).

The slicer includes all the dates from my Date table, I would like that the slicer only shows the dates which are available in the Satisfaction table.

There is a simple one to many relationship with a "Single Cross" filter direction.

If I set it to "Both" Cross filter direction, it works, but that is not what I want. This would bring me difficulties in future.

I tried to create a Dax function to achieve my goal, but failed:

MyMeasure = 

IF (
    SELECTEDVALUE('Calendar Lookup'[Date]) >= MIN(Satisfaction[Date]),
    1,
    0
)

I added this function to the filter of the visual and hoped that if I filter our any "0" values it would filter out the dates which are not available in the Satisfaction table.

But it does not work.

What I notice is that the value (when added the measure to the card), is that the measure displays "1" when the date selected on the slicer is bigger than the minimum date of the satisfaction table and "0" when it is lower.

How can I change my measure so it works? Or is there another solution?

Thank you for any help.

davidebacci · Accepted Answer · 2023-11-13 14:32:30Z

1

Use this:

COUNTROWS('Your fact table')

Add to the slicer as a visual filter and set it to not blank or > 0

answered Nov 13, 2023 at 14:32

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Serge Inácio Over a year ago

WOW! was it really that simple? Thanks a lot :)

davidebacci Over a year ago

Lol. I didn't know that was a "feature". Thanks

Serge Inácio Over a year ago

Me neither, lol

Collectives™ on Stack Overflow

Exclude dates from Date Table which are not available in the fact table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related