Id with multiple values

[Anonymous]

Hello, currently I have tow tables: contracts and Units (building units) related by ContractID, and based on the final date of the contract, I have calculated if that specific contractID is currently with a tenant or vacant, thus letting me see which units are vacant or not.
The problem is that one unit could have had multiple contracts over time and therefore it would show the unit duplicated with both vacant and with tenant values:

Im not being able to come up with a way to, when having a unitID with multiple contracts, just keep the one that has the last enddate without losing the ones that are vacant (contracts have expired)

Could you help me?

Thank you!

[SpartaBI]

@Anonymous you have a scenario of many to many relationship (not to be confused with many to many cardinality which is much less complicated scenario). You need a bridge table with unique combinations of contract and units between these two tables. This is a technique you need to learn, but I got you. Start with this free video by SQLBI.COM: https://www.youtube.com/watch?v=wRSJ6TYjEu0
If you need more look for everything you can from sqli regarding this, they have everything and explained in the best way.
 

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

Hi, thanks for your response, im not sure though that this is a many to many relationship, since a unit can have multiple contracts but there cant be a contract with multiple units.

In the end, what I think its needed is a way to filter that when an unitID has multiple values, just filter the one with the latest enddate. I cannot come up with the dax way of doing it even though it doesnt sound that hard.

😐

[SpartaBI]

oh, so no. it's not a many to many 🙂
So I don't understand the issue :))
Can you maybe share a sample PBIX and explain there hard coded the desired result?

[Anonymous]

So basically, what i want is to display a table in which I can see all units (building units) and see if they are currently vacant or with a tenant (based on the enddate of their contract/ this is done).

As of right now, I have a table in which I can see all units, but some are dupliacted showing both the value vacant and with tenant, which happens because they used to have a tenant, whos contract expired, and then a new one entered (example: unit 007 in table below).

The thing is, that if I filter by enddate (lets say, enddate after today), then the table will stop displaying the units that are vacant., since they currently have no contract in place.

So, the desired end result would be to display all units, based on their last contract ( so no dupliacted values).

Dont know if this is helpfull.

Thanks again!