Counting rows with filtering multiple dates from one date slicer

[jvelling]

Hi everyone,

I would like to use a dax formula to count the number of rows under a specific date restriction, by using one date slicer. 

The goal is to count the available objects, by only including the rows where:

Selected date < Arrival date 

Selected date >= Departure Date

Selected date < blocked from

Selected date >= blocked until 

Selected date < Not available from   

Considering the model below, I tried the following dax formula: 

AVAILABILITY =

CALCULATE(

COUNT('DATEKEY Tabel'[CODE]),

FILTER(

ALLSELECTED('DATEKEY Tabel'[ARRIVAL]),

'DATEKEY Tabel'[ARRIVAL] > MAX(Kalender[Datum])),

FILTER(

ALLSELECTED('DATEKEY Tabel'[DEPARTURE]),

'DATEKEY Tabel'[DEPARTURE] <= MAX(Kalender[Datum])),

USERELATIONSHIP(Kalender[Datum], 'DATEKEY Tabel'[DEPARTURE]),

FILTER(

ALLSELECTED('DATEKEY Tabel'[BLOCKED FROM]),

'DATEKEY Tabel'[BLOCKED FROM] > MAX(Kalender[Datum])),

USERELATIONSHIP(Kalender[Datum], 'DATEKEY Tabel'[BLOCKED FROM]),

FILTER(

ALLSELECTED('DATEKEY Tabel'[BLOCKED UNTIL]),

'DATEKEY Tabel'[BLOCKED UNTIL] < MAX(Kalender[Datum])),

USERELATIONSHIP(Kalender[Datum], 'DATEKEY Tabel'[BLOCKED UNTIL]),

FILTER(

ALLSELECTED('DATEKEY Tabel'[NOT AVAILABLE FROM]),

'DATEKEY Tabel'[NOT AVAILABLE FROM] >= MAX(Kalender[Datum])),

USERELATIONSHIP(Kalender[Datum], 'DATEKEY Tabel'[NOT AVAILABLE FROM])

)

There is an active relationship between Date and Arrival. The other relationships are inactive. However, the Dax formula does not work. Does anyone have an alternative for this?

Kind regards,

Jaap

[PaulDBrown]

@jvelling Have you tried using ALL(Date Key Tabel) instead of the ALLSELECTED(...) (you might also need to change the MAX expressions and use SELECTEDVALUE) ?

[jvelling]

Hi @PaulDBrown ,

Thanks for your response. Unfortunately this doesn't work either. In both cases I get an error saying the model is ambigiuous. 

[PaulDBrown]

@jvelling 

you could try using CROSSFILTER. 

Assuming the active relationship between the date table and the fact table  is between the column  Date table [Date] and  the column Fact table [Arrival] (adjust accordingly), and your present measure (as you have above) is [AVAILABILITY]

New AVAILABILITY =

CALCULATE( [AVAILABILITY], CROSSFILTER(Fact Table [Arrival], Date table [Date], none)

If that doesn't work you need a duplicate of your date table (with inactive relationships to your fact table) to use as a slicer. You can then use USERELATIONSHIP to calculate the values and SELECTEDVALUE as the reference in the FILTER expressions 

[jvelling]

Hi @PaulDBrown ,

Thanks again for your quick response. I have tried both of your suggestions, but in both cases the ambiguity remains. To verify if I am following you correctly in your second suggestion:

1. I created the following model

2. I have put kalender2 date in the slicer

3. I have changed the measure to: 

AVAILABILITY =

CALCULATE(

COUNT('DATEKEY Tabel'[CODE]),

FILTER(

ALL('DATEKEY Tabel'[ARRIVAL]),

'DATEKEY Tabel'[ARRIVAL] > SELECTEDVALUE(Kalender2[Datum])),

USERELATIONSHIP(Kalender2[Datum], 'DATEKEY Tabel'[DEPARTURE]),

FILTER(

ALL('DATEKEY Tabel'[DEPARTURE]),

'DATEKEY Tabel'[DEPARTURE] <= SELECTEDVALUE(Kalender2[Datum])),

USERELATIONSHIP(Kalender2[Datum], 'DATEKEY Tabel'[DEPARTURE]),

FILTER(

ALL('DATEKEY Tabel'[BLOCKED FROM]),

'DATEKEY Tabel'[BLOCKED FROM] >= SELECTEDVALUE(Kalender2[Datum])),

USERELATIONSHIP(Kalender2[Datum], 'DATEKEY Tabel'[BLOCKED FROM]),

FILTER(

ALL('DATEKEY Tabel'[BLOCKED UNTIL]),

'DATEKEY Tabel'[BLOCKED UNTIL] < SELECTEDVALUE(Kalender2[Datum])),

USERELATIONSHIP(Kalender2[Datum], 'DATEKEY Tabel'[BLOCKED UNTIL]),

FILTER(

ALL('DATEKEY Tabel'[NOT AVAILABLE FROM]),

'DATEKEY Tabel'[NOT AVAILABLE FROM] >= SELECTEDVALUE(Kalender2[Datum])),

USERELATIONSHIP(Kalender2[Datum], 'DATEKEY Tabel'[NOT AVAILABLE FROM])

)

Is this following along your suggestion?

Thanks a lot so far!

[PaulDBrown]

@jvelling 

I think the issue is with the FILTER expressions. You are removing the filter from the fact table with the ALL expression when you should be removing the filters from the date table FILTER(ALL(Date table), .....

(Sorry, I should have seen that before, but the name of the table DATEKEY made me think of a Date table, when in fact your date table is "Kalender")

try that and see if it solves it