Forum Discussion

jvelling's avatar
jvelling
Frequent Visitor
5 years ago

Counting rows with filtering multiple dates from one date slicer

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

 

 

 

Need Help

5 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    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's avatar
      jvelling
      Frequent Visitor
      5 years ago

      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's avatar
        PaulDBrown
        Community Champion
        5 years ago

        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