Forum Discussion
Id with multiple values
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!
4 Replies
- SpartaBI
Community Champion
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.
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?
