CODEWITHSUNDEEP
powerbidaxpowerbi-desktopmeasure
Serge Inácio
Serge Inácio

Reputation: 1382

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

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.

Upvotes: 1

Views: 486

Answers (1)

davidebacci
davidebacci

Reputation: 30289

Use this:

COUNTROWS('Your fact table')

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

Upvotes: 1

Related Questions