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

     

     

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

 

 

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