Forum Discussion
Id with multiple values
Community ChampionAnonymous 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.
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?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!