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 ...
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] )
    )
)

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Even if I remove the relationships it doesn't count anything 😞 

 

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 

which date column are you using in your visual?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Month-Year in the DateQuery table. I have tried with a blank table and it doesn't show anything

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Anonymous 

      I thought you want to slice by this column. I don't understand the meaning of using a blank table. However, please try the follow which I guess should work even in a card visual. 

      Count of property empty =
SUMX (
    VALUES ( DateQuery[Month-Year] ),
    CALCULATE (
        COUNTROWS (
            FILTER (
                hraptvpd,
                hraptvpd[prev_tency_end] < MIN ( DateQuery[Date] )
                    && hraptvpd[next_tency_st] > MAX ( DateQuery[Date] )
            )
        )
    )
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Still nothing 😞