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 

 

When I try to create a relationship between the tables "TopRankingQuery" and "Overrun Data" using the OrgName field, I get the error below that does not allow me to make the relationship active.

 

 

 Any ideas or suggestions to fix this issue? The goal is to have the matrix report below with two columns (Overrun and Total Revenue). However, the Total Revenue column shows the same amount.

 

Thank you for your time and effort. Much appreciated!!

 

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

     

5 Replies

  • 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