Forum Discussion

melice's avatar
melice
New Member
2 years ago
Solved

Lookup with complex conditions

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!

 

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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 .

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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's avatar
      melice
      New Member
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.