Lookup with complex conditions

melice 

output

data used : 

measure : 

Measure 4 = 
VAR rule = MAX(ruletable[rule])
var att1 = SELECTEDVALUE(ruletable[attribute1])
var att2 = SELECTEDVALUE(ruletable[attribute2])
var att3 = SELECTEDVALUE(ruletable[attribute3])
var ds = 
FILTER(
    usertable,
    usertable[role] = rule
)

var ds_att1 = 
    FILTER(
        ds,
        SWITCH(
            TRUE(),
            att1<> "*" , 
        usertable[userattribute1] = att1 ,not ISBLANK( usertable[userattribute1]) )
)

var ds_att2 = 
    FILTER(
        ds_att1,
        SWITCH(
            TRUE(),
            att2<> "*" , 
        usertable[userattribute2] = att2,not ISBLANK( usertable[userattribute2]))
)

var ds_att3 = 
    FILTER(
        ds_att2,
        SWITCH(
            TRUE(),
            att3<> "*" , 
        usertable[userattribute3] = att3,not ISBLANK( usertable[userattribute3]))
)


var res = DISTINCT(SELECTCOLUMNS(ds_att3, [userid]))

return COUNTROWS(res)

    

let me know if that works for you .

If this answers your question  ,  mark it as the solution ✅ so can you can help  other people in the community find it easily .

Hi melice 

You can try the following sloution.

1.Create three measures in rule table.

Attribute1_type = IF(FORMAT(MAX(RuleTable[Attribute 1]),"")="*",CONCATENATEX(DISTINCT(UserTable[User Attribute 1]),[User Attribute 1],","),FORMAT(MAX(RuleTable[Attribute 1]),""))

Attribute2_type = IF(FORMAT(MAX(RuleTable[Attribute 2]),"")="*",CONCATENATEX(DISTINCT(UserTable[User Attribute 2]),[User Attribute 2],","),FORMAT(MAX(RuleTable[Attribute 2]),""))

Attribute3_type = IF(FORMAT(MAX(RuleTable[Attribute 3]),"")="*",CONCATENATEX(DISTINCT(UserTable[User Attribute 3]),[User Attribute 3],","),FORMAT(MAX(RuleTable[Attribute 3]),""))

2.Then create a measure.

Count =
COUNTROWS (
    FILTER (
        UserTable,
        CONTAINSSTRING ( [Attribute1_type], [User Attribute 1] )
            && CONTAINSSTRING ( [Attribute2_type], [User Attribute 2] )
            && CONTAINSSTRING ( [Attribute3_type], [User Attribute 3] )
            && [Role] = MAX ( RuleTable[Rule] )
    )
)

Output

Best Regards!

Yolo Zhu

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