Forum Discussion

RG010's avatar
RG010
Frequent Visitor
5 years ago
Solved

Data Relationships

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.  ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

HiRG010，

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 ：
 

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's avatar
    RG010
    Frequent Visitor
    5 years ago

    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.