Forum Discussion
Advocate VLookup from Same Table and return true or false
Hi, I need help with DAX to return TRUE or FALSE from the following data set. For Acct# = 11, I want to check other rows with same Acct#, and if Persons are different, I want to compare Bag with Bag Combo (this is multiple bags separates with hyphen) and if exists then return TRUE, otherwise FALSE.
- Are there other entries in the data set that have this account number? (i.e., is there more than one bag on a given account?)
- If Yes, does the Bag of the Owner on the current row overlap with the Bag Combos carried by any of the people on the other rows for that account?
for Row 1 below, 11=11, Bob<>Sue, HR doesnt exist in Sue's Bag Combo so return FALSE
for Row 2, 11=11, Sue <> Bob, TN exists in Bob's Bag Combo (HR-TN) so return TRUE.
| Row | Acct# | Bag | Person | Bag Combo | Need this |
| 1 | 11 | HR | Bob | HR-TN | FALSE |
| 2 | 11 | TN | Sue | TN | TRUE |
| 3 | 12 | HR | Bob | HR-TN | FALSE |
| 4 | 12 | TN | Bob | HR-TN | FALSE |
| 5 | 13 | TN | Sue | TN | FALSE |
| 6 | 13 | Sales | Hans | SGEN | FALSE |
| 7 | 14 | HR | Bob | HR-TN | FALSE |
| 8 | 15 | HR | Bill | HR | FALSE |
| 9 | 15 | TN | Sue | TN | FALSE |
| 10 | 16 | HR | Sally | HR-TN | FALSE |
| 11 | 16 | TN | Sally | HR-TN | FALSE |
| 12 | 16 | Sales | Hans | SGEN | FALSE |
| 13 | 17 | HR | Bill | HR | TRUE |
| 14 | 17 | TN | Sally | HR-TN | FALSE |
This works with your sample data.
Assuming yourtablename is Table1
Calculated Column = VAR myrow = [Row] VAR Act = [Acct#] VAR mybag = [Bag] VAR myperson = [Person] VAR SameAcc_DifRow = ADDCOLUMNS ( FILTER ( Table1, [Acct#] = Act && [Row] <> myrow && [Person] <> myperson ), "BagFound", SEARCH ( mybag, [Bag Combo], 1, 0 ) ) RETURN COUNTROWS ( FILTER ( SameAcc_DifRow, [BagFound] > 0 ) ) > 0Hi deepu299
Try this column to get Person with same bag.
See file attached as well
PersonWithSameBag = VAR myrow = [Row] VAR Act = [Acct#] VAR mybag = [Bag] VAR myperson = [Person] VAR SameAcc_DifRow = ADDCOLUMNS ( FILTER ( Table1, [Acct#] = Act && [Row] <> myrow && [Person] <> myperson ), "BagFound", SEARCH ( mybag, [Bag Combo], 1, 0 ) ) RETURN CONCATENATEX ( FILTER ( SameAcc_DifRow, [BagFound] > 0 ), [Person], "," )
3 Replies
- Zubair_Muhammad
Community Champion
This works with your sample data.
Assuming yourtablename is Table1
Calculated Column = VAR myrow = [Row] VAR Act = [Acct#] VAR mybag = [Bag] VAR myperson = [Person] VAR SameAcc_DifRow = ADDCOLUMNS ( FILTER ( Table1, [Acct#] = Act && [Row] <> myrow && [Person] <> myperson ), "BagFound", SEARCH ( mybag, [Bag Combo], 1, 0 ) ) RETURN COUNTROWS ( FILTER ( SameAcc_DifRow, [BagFound] > 0 ) ) > 0- deepu299
You are awesome. Thanks much Zubair_Muhammad , the solution worked perfectly. Is it possible to show the person with the same bag when True?
- Zubair_Muhammad
Hi deepu299
Try this column to get Person with same bag.
See file attached as well
PersonWithSameBag = VAR myrow = [Row] VAR Act = [Acct#] VAR mybag = [Bag] VAR myperson = [Person] VAR SameAcc_DifRow = ADDCOLUMNS ( FILTER ( Table1, [Acct#] = Act && [Row] <> myrow && [Person] <> myperson ), "BagFound", SEARCH ( mybag, [Bag Combo], 1, 0 ) ) RETURN CONCATENATEX ( FILTER ( SameAcc_DifRow, [BagFound] > 0 ), [Person], "," )
