Forum Discussion

SvenJacobs's avatar
SvenJacobs
Frequent Visitor
5 years ago

AND OR in Filter

I have a small issue and do not seem to get the DAX code correct.
 
Need to know the amount of flats occupied.
For this i do a count of contractid and i want to match 2 dates
Date 1 = DatumInHuizing (The date that they start living at us)
Date 2 = DatumUitHuizing (The date that they move out)
So Date 2 can be a date or be empty combining AND and OR gives always errors, the DAX below is without the OR part šŸ˜ž
 
FlatsBezetPerDatum = CALCULATE(COUNT('Bewoners_Contracten (Sharepoint)'[ContractId]),FILTER('Bewoners_Contracten (Sharepoint)','Bewoners_Contracten (Sharepoint)'[DatumInHuizing]>= FIRSTDATE(DateTable[Date])&&('Bewoners_Contracten (Sharepoint)'[DatumuithuizingZonderNull]<=LASTDATE(DateTable[Date]))))
Need Help

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    SvenJacobs 

    Please try:

    FlatsBezetPerDatum =
VAR __startdate =
    FIRSTDATE ( DateTable[Date] )
VAR __enddate =
    LASTDATE ( DateTable[Date] )
RETURN
    CALCULATE (
        COUNT ( 'Bewoners_Contracten (Sharepoint)'[ContractId] ),
        FILTER (
            'Bewoners_Contracten (Sharepoint)',
            'Bewoners_Contracten (Sharepoint)'[DatumInHuizing] >= __startdate
                && ( 'Bewoners_Contracten (Sharepoint)'[DatumuithuizingZonderNull] <= __enddate )
        ),
        'Bewoners_Contracten (Sharepoint)'[DatumInHuizing] <> BLANK()
    )
    • SvenJacobs's avatar
      SvenJacobs
      Frequent Visitor
      5 years ago

      The 2 DAX codes seem to work only I guess my thinking is not correct..... Why because if I have 100 flats i can not. Need to figure out how your HR thing worked. Because I want to know on a specific date in time the amount of flats that wher rented out.

       

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    SvenJacobs ,

    Could you  pls share your pbix  and result you want to display ?I need data to test .Remember to remove confident data.

     

    Best Regards

    Lucien