Data Relationships

[RG010]

Hi!

So I have 2 tables which I have imported in PowerBI and created visualizations from. 1st table is Data table and Second table is Target table. A representative dataset is attached below. 

The first table is a Job Name, its location and its start year with the Effective cost for each year. The second table is giving what is the Target for each Location for year 2021 only. The total of effective cost for each locaton needs to meet or exceed the Target for the year, in this case 2021 only. 

I created relationship between the two tables using Location name as follows - 

When i create a Matrix with Row = Location name (from Data table) and Column = Start Year (From data table) with Values = Target (from Target table), I get the following result -

Since there is no job entry for location A in 2020 and for Location C in 2022, The target values are empty in those cells. However, since I have defined target values only for 2021 in Target table, why is it showing up for other years as well in this matrix? Is there a way I can correct this? Thanks!

[Anonymous · Accepted solution]

Hi@RG010，

You can try measure like this to meet your needs：

Measure =

var a=CALCULATE(SUM('Table B'[Target]),FILTER(ALL('Table B'),'Table B'[Target Year]=MAX('Table A'[Start Year])&&'Table B'[Location Name]=MAX('Table A'[Location Name])))

Return

IF(a=BLANK(),"",a)

Here is the demo ，please try it ：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/Ebeu1cI73LBAmfkypYH... 

Hope it helps.

Best Regards,
Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[RG010]

Hi @Anonymous ,

Thanks for the demo. This helps to understand the issue. However, it did not solve the problem in my original dataset since for some of the Locations in Table A, for eg, Location B, there does not exist a Job Name entry for the year 2021. Hence, even after creating the measure, I cannot get the Target to display for a Location B for year 2021. Let me know if there is a way to get around this. Thanks in advance.

[PaulDBrown]

@RG010 

I suggest you change the relationship to one-to-many (from the target table to the data table). When building visuals you then use the fields from the target table (which will filter the data table) and add any further fields you may need from the data table. Any aggregations on numerical values or counts of text rows etc on both tables will then work seamlessly.

[RG010]

Thanks for the suggestion @PaulDBrown . However, due to some requirements in my original dataset, I can't use the Target year (from Target Table) as columns. I need to use the Start Year(from Data table) in the matrix. Implementing your suggestion to convert relationship from One -to - many still gave me the similar result as the screenshot in my original post. Any other suggestion would be really helpful. Thanks.