HELP ! Get Values from Another Table ( many-many relationship ) + New Column with Logical test

[Vrykolakas]

I've been struggling all day to get This result.
Due to the relationship between Table A and Table B, can't use Lookup, and the related() function is also showing me some issues. Maybe my logical thinking is the issue here... 

This is what I'm trying to achieve, I hope it's clear

- Two new columns should be added to Table A, Season ( from Table B ) and StatusCurrent, that does a logical test to return True if Season = YEAR(TODAY())

[v-zhangti · Accepted solution]

Hi, @Vrykolakas 

You can try the following methods. Add an index column to Table A in Power Query.

Column:

Season = 
Var _Maxseason=CALCULATE(MAX('Table B'[Season]),FILTER(ALL('Table B'),[id]=EARLIER('Table A'[id])))
Var _Maxindex=CALCULATE(MAX('Table A'[Index]),ALLEXCEPT('Table A','Table A'[id]))
Var _Minseason=CALCULATE(MIN('Table B'[Season]),FILTER(ALL('Table B'),[id]=EARLIER('Table A'[id])))
Return
IF([Index]=_Maxindex,_Maxseason,_Minseason)

StatusCurrent = IF(YEAR(TODAY())=[Season],TRUE(),FALSE())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[lbendlin]

For Many to x relationships you need to use RELATEDTABLE, not RELATED.

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523