Forum Discussion

chapmanbry's avatar
chapmanbry
Regular Visitor
6 years ago
Solved

Return value from reference table when multiple criteria match

Need help to accomplish the following.

 

I have the following tables:

 

Reference Table

 

GroupCriteria 1Criteria 2Criteria 3
HD01MRHRK, L
HD02MZRCA, B, C
HD03MSDCF, M

 

Production (Results) Table

 

Results DeviceResults 1Results 2Results 3
DVC1MRHRL
DVC2MZRCA
DVC3MSDCF, M
DVC4MRHRK
DVC5MSDCF

 

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:

 

GroupResults Device
HD01DVC1
HD02DVC2
HD03DVC3
HD01DVC4
HD03DVC5

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

2 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.