Forum Discussion

amart's avatar
amart
Frequent Visitor
3 years ago

Relationships and Dax Measures Issue

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!

7 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      amart
      Frequent Visitor
      3 years ago

      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's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        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

    • amart's avatar
      amart
      Frequent Visitor
      3 years ago

      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's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        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