Vlookup for any member of comma separated list in a different table

[Anonymous]

Hello everyone,

I am facing the below business case, and I have trouble finding a solution behind it.

Business Case

I have a Table 1 [Changes], where I have a specific Column A [Countries]. 

Column [Countries] can have different values either single values i.e. Total Europe or multiple, comma separated values i.e. Poland, Denmark, Spain. Structure of the table is as per below

ID              Change                    Countries

1                  XYZ                      Total Europe

2                  ABC                Poland, Denmark, Spain

There is a Table 2 [Segmentation], where each of the above countries takes a value, either BIG/SMALL/ALL. 

ID              Country                Attribute

1                Poland                     BIG

2                 Spain                    SMALL

3              Denmark                    BIG

4           Total Europe                 ALL

What I want PowerBI to do is the following:

For each row of the Table 1, check where the value of Column Countries belongs by vlookingup Table 2.

                - If Table 1 [Countries] = Total Europe, use "ALL"

                - If Table 1 [Countries] = Spain, use "SMALL"

                - If Table 1 [Countries] = Poland, use "BIG"

                - If Table 1 [Countries] = Denmark, Poland, Spain, use "ALL" (values belong in both categories)

I have found the respective solutions for Excel here
I can't figure it out though on PowerBI.... any ideas?

Thank you!!!

[Anonymous]

@selimovd,

I don't know how to thank you enough!!! Not only for providing the solution, but also building a new logic, that I can reapply across - SIMPLY THANK YOU!!!!!!

[selimovd]

Hey @Anonymous ,

you're welcome 😊

I'm happy you can use that solution for future approaches and maybe understand DAX a little better.

Best regards

Denis

[Anonymous]

@selimovd 

WOW!!! This works PERFECTLY!!!! there is one minor point: what happens when I have multiple values, which belong to the same group? For example I have 2 "SMALL", which are comma separated?

You can also guide me and I will try to build the code 😉

[selimovd · Accepted solution]

Hey @Anonymous ,

I guess you mean if there would be a row "Spain, Switzerland" for example and both would have the attribute "SMALL"?

You would have to check when there is a comma if all the values have the same attribute.
Check the following measure:

Country Attribute = 
// Check if there is a direct match from Countries to Country
VAR vDirectMatch =
    LOOKUPVALUE(
        'Table 2'[Attribute],
        'Table 2'[Country], 'Table 1'[Countries]
    )

// Check if by comma separated values have the same attribute
VAR vCurrentRowCountry = 'Table 1'[Countries]
VAR vMatchTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table 2', 'Table 2'[Country], 'Table 2'[Attribute] ),
        "@CountryExists", IFERROR ( SEARCH ( 'Table 2'[Country], vCurrentRowCountry ), BLANK () )
    )
VAR vFilteredMatchTable =
    SUMMARIZE (
        FILTER ( vMatchTable, [@CountryExists] <> BLANK () ),
        'Table 2'[Attribute]
    )
VAR vCheckOneValue = MAXX ( vFilteredMatchTable, 'Table 2'[Attribute] )

// Check if country contains a comma and set to ALL
VAR vSearch =
    IFERROR(
        SEARCH(
            ",",
            'Table 1'[Countries]
        ),
        BLANK()
    )

// Return correct value
RETURN
    SWITCH(
        TRUE(),
        vDirectMatch <> BLANK(), vDirectMatch, -- if there is a direct match
        COUNTROWS ( vFilteredMatchTable ) = 1, vCheckOneValue, -- if multiple countries have same attribute
        vSearch <> BLANK(), "ALL" -- if countries contains comma, return "ALL"
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd]

Hey @Anonymous ,

try the following calculated column. When there are multiple values separated by a comma it will return "ALL" and otherwise the related attribute:

Country Attribute =
VAR vDirectMatch =
    LOOKUPVALUE(
        'Table 2'[Attribute],
        'Table 2'[Country], 'Table 1'[Countries]
    )
VAR vSearch =
    IFERROR(
        SEARCH(
            ",",
            'Table 1'[Countries]
        ),
        BLANK()
    )
RETURN
    SWITCH(
        TRUE(),
        vDirectMatch <> BLANK(), vDirectMatch,
        vSearch <> BLANK(), "ALL"
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic