Forum Discussion
Relationships and Dax Measures Issue
Community SupportHi, amart
According to your description, you want to get the " $ / SF, Tenant Rent and Square Feet".
For your model relationships, one-to-many is fine.
But for your business logic, I am not very clear, for the data you provided I did not find the corresponding calculation logic.
How to calculate Figure 2 from Figure 1?
For your needs, can you provide us with sample data for each table and the final result you want and your calculation logic?
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hey v-yueyunzh-msft thank you for your help on this. I'm not exactly sure why i'm still having an issue. Below is the relationships I have setup, everything seems to match yours.
Everything flows through correctly when I setup the report for re_name anbd unit_name (below).
But when I add in tenant_name, the reports doesnt give me the correct output.
It seems like its still an issue with the relationships - any other thoughts?
Thanks again for your assistance.
- v-yueyunzh-msft
Hi , amart
Thanks for your quick response !
According to the relationship in your screenshot, it seems the 'Unit Data' and the 'Tenant Rent' are one-to many relationship and the related field is the [unit_id]?
Another, the 'Tenant Rent' and the 'Tenants' table are also the one-to many relationship.
But for the sample data you provide , it is all the "one-to-one" relationship in my test side:
So i am not fully understand the logic in your tables, can you re provide the complete data of the next two tables in the form of a table, so that they can form a one to many relationship?
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hey v-yueyunzh-msft thanks again for helping me work through this.
Yes, 'Unit Data' and 'Tenant Rent' are both one-to-many relationships on the 'unit_id' column.
For the limited sample data I gave you, the 'Tenant' and 'Tenant Rent' tables have only a one-to-one relationship, but in the real dataset they will have multiple rows with an additional date dimension. For this example it doesnt seem like thats the constraining issue.
When I look at create a query for re_name / unit_name / tenant_name / Tenant: Rent (measure), it gives me all of the tenants with their rent. But when I add Square Feet (measure) to it, it gives me a lot of repetiative data. When I remove tenant_name, then it shows the correct Tenant Rent (measure) and Square Feet (measure). My goal is to have re_name / unit_name / tenant_name / Tenant: Rent / Square Feet all in the same report.
Below is what i'm currently getting when I attempt to add in tenant_name.
So it seems like i'm missing something obvious with the relationships between the Tenants, Tenant Rent, and Unit Data Tables.
Again, thank you for your help on this!
- v-yueyunzh-msft
Hi , amart
Thanks for your quick response!
For your model relationships, show that you meet your expectations when you don't put 'Tenant Name', but you don't put it later. This is because your Tenants table is a multi-terminal table, and it will match multiple corresponding tenant names, which will automatically expand your table. First you need to determine what the logic of the Tenant Name you need to show is, because one rename corresponds to multiple unit_name and corresponds to multiple tenamt names, then placed in the visual will automatically expand.
For your question, you can provide more detailed test data and the data results you want in the end.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly