Using lookups to match multiple values in columns in Power BI/DAX

user_17829973 0
Viewed 984

I have a table of university unit data. The units have historically been delivered in two different lengths (under the same unit code): sessions and terms. It looks something like this:

UnitCode Year OfferedIn
UNIT001 2021 Session1
UNIT001 2021 Session2
UNIT002 2021 Session1
UNIT002 2021 Term4
UNIT003 2020 Session1
UNIT003 2020 Session3
UNIT003 2021 Term2

I need to slice various graphs by units that were offered in both styles, regardless of which actual year, session, or term. So the extra column would be as on the right below:

UnitCode Year OfferedIn OfferedInBoth
UNIT001 2021 Session1
UNIT001 2021 Session2
UNIT002 2021 Session1 TRUE
UNIT002 2021 Term4 TRUE
UNIT003 2020 Session1 TRUE
UNIT003 2020 Session3 TRUE
UNIT003 2021 Term2 TRUE

So I want to look at a value in the UnitCode column and see if it has both different kinds of values in the OfferedIn column (I can do 'contains' expressions for the types) in any row where the unit code is the same. How do I generate the OfferedInBoth column? (Or do I need to create a new table? I'd prefer to keep it as dynamic as possible, as the dataset will add new unit codes over time.)

dax powerbi
2 Answers

You can create a measure as below-

true_false = 

var current_row_unitcode = min('your_table_name'[UnitCode])

var count_sess = 
COUNTROWS(
    FILTER(
        ALL('your_table_name'),
        'your_table_name'[UnitCode] = current_row_unitcode
        && LEFT('your_table_name'[OfferedIn],4) = "Sess"
    )
)

var count_term = 
COUNTROWS(
    FILTER(
        ALL('your_table_name'),
        'your_table_name'[UnitCode] = current_row_unitcode
        && LEFT('your_table_name'[OfferedIn],4) = "Term"
    )
)

return if(
    count_sess >=1 && count_term >= 1, "True",""
)

Output-

strong text

user_36523450

You could create a calculated column using the DAX code below. Replace "Table1" references with the name of your table and add any session/term values applicable to the SWITCH statements.

OfferedInBoth = 
VAR unitCode = Table1[UnitCode]
VAR unitCodeTable = FILTER(Table1, Table1[UnitCode] = unitCode)

VAR containsSession = 
    SWITCH(
        TRUE, 
        CONTAINS(unitCodeTable, Table1[OfferedIn], "Session1"), TRUE(), 
        CONTAINS(unitCodeTable, Table1[OfferedIn], "Session2"), TRUE(), 
        CONTAINS(unitCodeTable, Table1[OfferedIn], "Session3"), TRUE(), 
        CONTAINS(unitCodeTable, Table1[OfferedIn], "Session4"), TRUE(), 
        FALSE
    )

VAR containsTerm = 
    SWITCH(
        TRUE, 
        CONTAINS(unitCodeTable, Table1[OfferedIn], "Term1"), TRUE(), 
        CONTAINS(unitCodeTable, Table1[OfferedIn], "Term2"), TRUE(), 
        CONTAINS(unitCodeTable, Table1[OfferedIn], "Term3"), TRUE(), 
        CONTAINS(unitCodeTable, Table1[OfferedIn], "Term4"), TRUE(), 
        FALSE
    )

RETURN containsSession && containsTerm
user_178844140
Related