Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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 |
Parent | Child | Valid? | Why Valid? |
1234 | 23456 | Yes | Parent ID 1234 is in Master list - tag all as Y |
1234 | 65446 | Yes | Parent ID 1234 is in Master list - tag all as Y |
1234 | 45688 | Yes | Parent ID 1234 is in Master list - tag all as Y |
1234 | 15385 | Yes | Parent ID 1234 is in Master list - tag all as Y |
1235 | 15348 | Yes | Child ID 15318 is in the Master lig - tag all as Y |
1235 | 15318 | Yes | Child ID 15318 is in the Master lig - tag all as Y |
1235 | 55784 | Yes | Child ID 15318 is in the Master lig - tag all as Y |
1235 | 44775 | Yes | Child ID 15318 is in the Master lig - tag all as Y |
1236 | 9665 | No | Not valid - neither ID is in master list |
1236 | 8896 | No | Not valid - neither ID is in master list |
1236 | 6687 | No | Not valid - neither ID is in master list |
1236 | 6698 | No | Not valid - neither ID is in master list |
Solved! Go to Solution.
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.
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.
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
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |