Forum Discussion

silverdale9999's avatar
silverdale9999
Helper I
4 years ago
Solved

Cross Referencing multiple linked values

First of all - struggling to come up with a useful title for this one so apologies in advance.   If anyone is struggling for ideas for fun on Saturday, might I suggest you consider this small probl...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, silverdale9999 ;

    You could create a measure,

    Measure = 
var _contain=SUMMARIZE(FILTER(ALL('Table'),[Child] in ALLSELECTED(Master[List]) || [Parent] in ALLSELECTED(Master[List])),[Parent])
return IF(MAX('Table'[Parent]) in _contain,"Yes","No")

    Or a column

    Column = 
var _contain=SUMMARIZE(FILTER(ALL('Table'),[Child] in ALLSELECTED(Master[List]) || [Parent] in ALLSELECTED(Master[List])),[Parent])
return IF([Parent] in _contain,"Yes","No")

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

David-Ganor's avatar
David-Ganor
Resolver II
4 years ago

Hi silverdale9999 

I think it can be accomplished using 2 calculated columns.

The general logic can be simplified to - 

If the parent is valid than all its children are valid.

1st column - is parent valid:

Use lookup and if

If lookup of child is true or lookup of parent is true --> than return the Parent ID.

 

2nd column - search if current row Parent ID is in the 1st column of all the table

 

Var this_row_parent=Table[Parent]

Var rows_count=

Calculate(countrows(Table)

,Filter(Table

,Table[1st column]=this_row_parent)

Return

If(row_count>0,"Valid","Not Valid")

 

Hope it helps,

David