Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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 problem that I have 😏

 

I have two different sets of data:

2 Columns in one set of data:

Parent - Reference Numbers which may be duplicated if there are 'child' refs.

Child - Related to the Parent Reference (these will not be duplicated)

1 Column in the second set of data.

Master List - Reference numbers, which may be either Parent or Child.  These are what I am trying to validate against.

 

My aim is to tag the Parent or Child refs as Valid with a Y or N.

Criteria for this is:

  • If the Parent Ref is in the Master List, then the Parent and All Child ID's are valid.
  • If any of the Child Refs are in the Master List, then that Child ID, as well as the Master ID and all other Child ID's are tagged as valid.
  • If neither the Child or Parent are in the Master list, they are not valid.


I have no issues with tagging the individual references, but what I can't figure out is how to start tagging the all of the other linked refs that are not in the master list.  (The bit underlined above).  Possibly I've over complicated it in my head and now can't find a way forward.

 

Happy to listen to any suggestions at all!  Thanks!

 

Example data and results are here:

Master List
1234
15318



ParentChildValid?Why Valid?
123423456YesParent ID 1234 is in Master list - tag all as Y
123465446YesParent ID 1234 is in Master list - tag all as Y
123445688YesParent ID 1234 is in Master list - tag all as Y
123415385YesParent ID 1234 is in Master list - tag all as Y
123515348YesChild ID 15318 is in the Master lig - tag all as Y
123515318YesChild ID 15318 is in the Master lig - tag all as Y
123555784YesChild ID 15318 is in the Master lig - tag all as Y
123544775YesChild ID 15318 is in the Master lig - tag all as Y
12369665NoNot valid - neither ID is in master list
12368896NoNot valid - neither ID is in master list
12366687NoNot valid - neither ID is in master list
12366698NoNot valid - neither ID is in master list
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1645497737651.png

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.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1645497737651.png

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
Resolver II
Resolver II

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.