Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Active relationship causing count to be wrong

I'm trying to count number of empty propertys by counting the previous tenancy numbers that exist in the empty property table. "hraptvpd"
It's worth mentioning that hraptvpd is directly quering the database. 
 
hraptvpd has an active relationship of next_tency_st to DateQuery[Date]
It also has an inactive relationship of prev_tency_end to DateQuery[Date]
 
The only way I know it was empty (it could be empty multiple times) is the gap in dates between the previous tenancy end (hraptvpd[prev_tency_end]) and next tenancy start (hraptvpd[next_tency_st])
 
I have tried:
Which gives the error: 
Count of property empty =
CALCULATE( USERELATIONSHIP(hraptvpd[prev_tency_end], DateQuery[Date]),
COUNT(hraptvpd[prev_tency]),
DATESBETWEEN(DateQuery[Date], hraptvpd[prev_tency_end], hraptvpd[next_tency_st])
 
)
 ------------------------------------------------------
Count of property ID =
CALCULATE(
COUNT(hraptvpd[prev_tency]),
FILTER(hraptvpd, hraptvpd[prev_tency_end] < DateQuery[End of Month] && hraptvpd[next_tency_st] >= DateQuery[End of Month])
 
)
The second solution above can only count the number of tenancies that started on the end of month: 

If anyone can help I'd be enternally grateful. 

COUNT
datesbetween
DAX
Help
relationships
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 

    first you have to deactivate the active relationship. Or otherwise you need add REMOVEFILTERS ( DateQuery ) with CALCULATE to the following code which considers the property empty only if it is epmty in the whole period. 

     

    Count of property empty =
COUNTROWS (
    FILTER (
        hraptvpd,
        hraptvpd[prev_tency_end] < MIN ( DateQuery[Date] )
            && hraptvpd[next_tency_st] > MAX ( DateQuery[Date] )
    )
)

     

     

12 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Maybe:

    Count of property empty =
CALCULATE( COUNT(hraptvpd[prev_tency]),
USERELATIONSHIP(hraptvpd[prev_tency_end], DateQuery[Date]),
DATESBETWEEN(DateQuery[Date], hraptvpd[prev_tency_end], hraptvpd[next_tency_st])
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Greg_Deckler 
      I get this error:


      to give an example of the data:

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 

    Hi Dan

    kindly share a screenshot of the data model tab

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      tamerj1 

      Please ignore the mess. You wouldn't want to see the rest.  😛 

      1. is hraptvpd[next_tency_st] to DateQuery[Date] (this is active)

      2. is hraptvpd[prev_tency_end] to DateQuery[Date] (this is inactive)

      3. is hraptvpd[act_end_dt] to DateQuery[Date] (this is inactive)

       

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi Anonymous 

        first you have to deactivate the active relationship. Or otherwise you need add REMOVEFILTERS ( DateQuery ) with CALCULATE to the following code which considers the property empty only if it is epmty in the whole period. 

         

        Count of property empty =
COUNTROWS (
    FILTER (
        hraptvpd,
        hraptvpd[prev_tency_end] < MIN ( DateQuery[Date] )
            && hraptvpd[next_tency_st] > MAX ( DateQuery[Date] )
    )
)