Create Relationship to Add Field to Matrix Report

[whenriques]

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!!

[Anonymous]

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]

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 · Accepted solution]

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]

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

[whenriques]

Below is a snapshot of the datasets: Any ideas?