Forum Discussion

whenriques's avatar
whenriques
Helper I
4 years ago
Solved

Create Relationship to Add Field to Matrix Report

Any chance you can, please, help me create a relationship to add the '"Total Revenue" to the matrix report below:   I have the relationship of the tables using "Period." See below    ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi whenriques ,

     

    Please check if this is what you want.

     

    Total Overrun =
CALCULATE (
    SUM ( 'Overrun Data'[amount] ),
    USERELATIONSHIP ( TopRankingQuery[OrgName], 'Overrun Data'[Organization] ),
    FILTER ( 'Overrun Data', 'Overrun Data'[OFRmapping] IN { "overrun" } )
)

     

    The basic logic is to create an inactive relationship and use USERELATIONSHIP() function to active the relationship while doing the calculation.

     

    Best Regards,

    Jay

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi whenriques ,

 

Both tables have an active relationship between date table so you can not create another active relationship between them.

You could create an inactive relationship(uncheck the 'make this relationship active').

Then create a measure like:

measure = calculate(sum([Total Revenue]),userelationship(TopRankingQuery[OrgName],Overrun Data[OrgName]))

https://docs.microsoft.com/en-us/dax/userelationship-function-dax 

 

Best Regards,

Jay

  • whenriques's avatar
    whenriques
    Helper I
    4 years ago

    Hi Jay,

    Anonymous 

     

    Thank you so much for taking a look. I believe you have the solution to my issue! Can you please take a look at the .pbix file below?

    https://swcacorp-my.sharepoint.com/:u:/g/personal/whenriques_swca_com/ERxE1BKF0ZtKs6VSEZ_hsnsBEpkuhI3l5qjzRKdUqweR1A

     

    Is it possible to add Direct Labor from one table and Total Overrun from a different table to a matrix report?

     

    Both tables are related to the Date table based on Period. However, when I try to create a relationship between OrgName and Organization Name, Power BI cannot create this Many-Many relationship. Any thoughts or suggestions?

     

     

     

     

    How would you create an inactive relationship and then create the measure using userelationship?

     

    Ultimately, I would like to have a matrix report with two columns (Direct Labor and Overrun) for period 202203. However, the values for Overrun as the same. See below:

     

     

    Any ideas from you would be greatly appreciated. I sincerely thank you for your time and support.

     

    Cheers,

     

    Willer

     

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi whenriques ,

       

      Please check if this is what you want.

       

      Total Overrun =
CALCULATE (
    SUM ( 'Overrun Data'[amount] ),
    USERELATIONSHIP ( TopRankingQuery[OrgName], 'Overrun Data'[Organization] ),
    FILTER ( 'Overrun Data', 'Overrun Data'[OFRmapping] IN { "overrun" } )
)

       

      The basic logic is to create an inactive relationship and use USERELATIONSHIP() function to active the relationship while doing the calculation.

       

      Best Regards,

      Jay

       

      • whenriques's avatar
        whenriques
        Helper I
        4 years ago

        Hi Anonymous,

        You are 'da man!!!! I have been trying to solve this question for almost a week by watching tons of videos, spending hours with this group, and working with my model for hours.... 🙂

         

        Thank you so very much for your time and for helping me solve this Power BI question! I am grateful for your help and assistance and would love to buy you a cup of your favorite beverage in case you come to the Phoenix, AZ area!!

         

        Cheers and thanks again,

         

        Willer