Return value from reference table when multiple criteria match

[chapmanbry]

Need help to accomplish the following.

I have the following tables:

Reference Table

Group	Criteria 1	Criteria 2	Criteria 3
HD01	MR	HR	K, L
HD02	MZ	RC	A, B, C
HD03	MS	DC	F, M

Production (Results) Table

Results Device	Results 1	Results 2	Results 3
DVC1	MR	HR	L
DVC2	MZ	RC	A
DVC3	MS	DC	F, M
DVC4	MR	HR	K
DVC5	MS	DC	F

Need to match device to group based on all criteria columns.

Results  = Group based on the following conditions (Criteria 1 | Criteria 2 | Criteria 3)

| = or

End result required:

Group	Results Device
HD01	DVC1
HD02	DVC2
HD03	DVC3
HD01	DVC4
HD03	DVC5

[v-alq-msft · Accepted solution]

Hi, @chapmanbry 

Based on your description, you may create a calculated column or a measure as below. The pbix file is attached in the end.

Calculated column:
Re Column = 
var tab = 
FILTER(
    Reference,
    IF(
       SEARCH(Production[Results 1],Reference[Criteria 1],,-1)>0,
       TRUE(),FALSE()
    )&&
    IF(
       SEARCH(Production[Results 2],Reference[Criteria 2],,-1)>0,
       TRUE(),FALSE()
    )&&
    IF(
        SEARCH(Production[Results 3],Reference[Criteria 3],,-1)>0,
        TRUE(),FALSE()
    )
)
return
CONCATENATEX(
    tab,
    [Group],
    ","
)

Measure:
Re Measure = 
var tab = 
FILTER(
    Reference,
    IF(
       SEARCH(SELECTEDVALUE(Production[Results 1]),Reference[Criteria 1],,-1)>0,
       TRUE(),FALSE()
    )&&
    IF(
       SEARCH(SELECTEDVALUE(Production[Results 2]),Reference[Criteria 2],,-1)>0,
       TRUE(),FALSE()
    )&&
    IF(
        SEARCH(SELECTEDVALUE(Production[Results 3]),Reference[Criteria 3],,-1)>0,
        TRUE(),FALSE()
    )
)
return
CONCATENATEX(
    tab,
    [Group],
    ","
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chapmanbry]

@v-alq-msft that works perfectly, thank you!