Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dan492813
Helper II
Helper II

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: 
Dan492813_1-1653651730825.png
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: 
Dan492813_0-1653651339205.png

If anyone can help I'd be enternally grateful. 

1 ACCEPTED SOLUTION

Hi @Dan492813 

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

 

 

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

@Dan492813 

Hi Dan

kindly share a screenshot of the data model tab

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

 

 

Dan492813_0-1653664142870.png

 

Hi @Dan492813 

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

 

 

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

Dan492813_0-1653689416027.png

 

Hi @Dan492813 

which date column are you using in your visual?

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

@Dan492813 

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

Still nothing 😞 

Dan492813_0-1653774929214.png

 

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

How would we do this? 

Greg_Deckler
Super User
Super User

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 
I get this error:

Dan492813_0-1653654073027.png


to give an example of the data:

Dan492813_1-1653654271501.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors