Active relationship causing count to be wrong

[Anonymous]

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. 

[tamerj1]

@Anonymous 

Hi Dan

kindly share a screenshot of the data model tab

[Anonymous]

@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 · Accepted solution]

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]

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

[tamerj1]

Hi @Anonymous 

which date column are you using in your visual?

[Anonymous]

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

[tamerj1]

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

Still nothing 😞 

[tamerj1]

Good Morning @Anonymous 
I need to connect with you possible on Monday

[Anonymous]

How would we do this? 

[Greg_Deckler]

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

@Greg_Deckler 
I get this error:

to give an example of the data: