Lookup with complex conditions

[melice]

Hi everyone,

so, I have a tricky lookup situation and cannot find a query that works. 

I have two tables: UserTable and RuleTable. Every user has certain attributes and a role that is assigned based on those attributes. 

The RuleTable stores the exact attribute combination that needs to be satisfied in order to get that role assigned. 

One role can have multiple attribute combinations (hence the different index number), and I need to find out exactly what rule criteria each user meets. The major problem is that the rules can have "*" values, which means that any user attribute value is accepted. This is the main reason why I cannot do a simple "lookupvalue". 

can you suggest how I could get the desired result, as shown below?

Many many thanks in advance!

[Daniel29195 · Accepted solution]

@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 .

[Daniel29195 · Accepted solution]

@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 .

[melice]

Unfortunately, I get an "out of memory" error when using the measure. The actual data set is much larger. thank you for the suggestion, though. Looking through it, it seems this would have solved my issue. I appreciate it!

[Anonymous · Accepted solution]

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.