Lookup from Same Table and return true or false

[deepu299]

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

[Zubair_Muhammad · Accepted solution]

@deepu299 

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

[Zubair_Muhammad · Accepted solution]

@deepu299 

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 · Accepted solution]

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], "," )