topic Re: Active relationship causing count to be wrong in DAX Commands and Tips

Active relationship causing count to be wrong

Anonymous — Fri, 27 May 2022 11:58:25 GMT

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.

Re: Active relationship causing count to be wrong

Greg_Deckler — Fri, 27 May 2022 12:14:37 GMT

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

Re: Active relationship causing count to be wrong

Anonymous — Fri, 27 May 2022 12:24:42 GMT

Greg_Deckler
I get this error:

to give an example of the data:

Re: Active relationship causing count to be wrong

tamerj1 — Fri, 27 May 2022 14:36:05 GMT

Anonymous

Hi Dan

kindly share a screenshot of the data model tab

Re: Active relationship causing count to be wrong

Anonymous — Fri, 27 May 2022 15:11:32 GMT

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)

Re: Active relationship causing count to be wrong

tamerj1 — Fri, 27 May 2022 16:17:46 GMT

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

Re: Active relationship causing count to be wrong

Anonymous — Fri, 27 May 2022 22:10:28 GMT

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

Re: Active relationship causing count to be wrong

tamerj1 — Sat, 28 May 2022 02:07:53 GMT

Hi Anonymous

which date column are you using in your visual?

Re: Active relationship causing count to be wrong

Anonymous — Sat, 28 May 2022 20:27:16 GMT

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

Re: Active relationship causing count to be wrong

tamerj1 — Sat, 28 May 2022 20:41:08 GMT

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

Re: Active relationship causing count to be wrong

Anonymous — Sat, 28 May 2022 21:55:44 GMT

Still nothing 😞

Re: Active relationship causing count to be wrong

tamerj1 — Sun, 29 May 2022 03:23:14 GMT

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

Re: Active relationship causing count to be wrong

Anonymous — Mon, 30 May 2022 18:47:45 GMT

How would we do this?