Relationships and Dax Measures Issue

[amart]

Hey everyone, i'm still relatively new to DAX but i'm getting an issue when i'm trying to roll up the report to give me specifc measures of $ / SF, Tenant Rent and Square Feet. It seems like its an issue with the relationships. Below is an example of the real data i'm working with.

Below is what I get when I run the report.

This is an example of the desired result.

I would like to

• Show SF for all units - regardless if it has a tenant associated with it.
• Show all Tenants $/SF and Rent

Any help on this would be great, I feel like i'm missing something obvious. Thank you!

[v-yueyunzh-msft]

Hi, @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

[amart]

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

[amart]

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

[amart]

Hey @v-yueyunzh-msft thanks for responding to my post. 

Below is a sample of the data, measures and report setup. To be clear Figure #2 from above is something I put together manually to show what I would ideally like to see from this report, Figure #1 is what I current see.

Unit Data

Tenant Rent

Tenants

Real Estate

Measures

$ / SF = [Sum of rent]/[Square Feet]

Square Feet = SUMX('Unit Data',[SF])

Tenant: Rent =SUMX('Tenant Rent',[rent])

Current Report Setup

This is the current output i'm getting

Any help much appreciated! Thank you!

[v-yueyunzh-msft]

Hi , @amart 

Thank you for your quick response and your detailed data.

I create the data is the same as yours. In Power BI Desktop, i make this relationship between tables:

Then we need to create a measure like this:

$/SF = DIVIDE(SUM( 'Unit Data'[SF]), SUM('Tenant Rent'[rent]) )

Then we just need to put the field we need on the visual and we can meet your need:

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