CODEWITHSUNDEEP
powerbidax
LukeWarm
LukeWarm

Reputation: 27

DAX IF(OR( and LOOKUPValue in one

Very new to using PowerBI & DAX but wanting to see if it is the better option for some of my reporting.

I'm trying to replicate a formula that I have in Excel within PowerBI but just cant seem to do it!

The formula is an IF statement with OR/AND (nested) and then a vlookup at the end. I shortened part of the IF(OR using a named range and Count - see below

IF(AND(Client_Name="Client 1",Scheme_Name="Scheme 1"),"Team 1",IF(AND(Client_Name="Client 2",Scheme_Name="Scheme 2"),"Team 2",IF(AND(Client_Name="Dummy",Handling_Team_Name="Team 3"),"Team 3",IF(COUNTIF(C_Team_Helper,Client_Name),VLOOKUP(Scheme_Name,'Team Helper 123'!$A:$C,3,FALSE),""))))

with 'C_Team_Helper' being the named range which is just a column found on another sheet that contains a list of clients.

struggling to mimic this using DAX! I have the two sheets and my current failed attempt is the following

ClientTrigger = 
SWITCH(
    TRUE(),
    Sheet1[Client Name] = "Client 1" && Sheet1[Scheme Name] = "Scheme 1" ,"Team 1",
    Sheet1[Client Name] = "Cleint 2" && Sheet1[Scheme Name] = "Scheme 2" ,"Team 2",
    Sheet1[Client Name] = "Dummy" && Sheet1[Scheme Name] = "Scheme 3", "Team 3",

    Sheet1[Client Name] = CONTAINS('Team Helper - Setup','Team Helper - Setup'[Team Helper],Sheet1[Client Name]),

        CALCULATE (
        FIRSTNONBLANK ( 'Team Helper - Setup'[Team], 1 ),
        FILTER ( ALL ( 'Team Helper - Setup'), 'Team Helper - Setup'[Scheme Name] = Sheet1[Scheme Name])
        ))

Really appreciate any help, I've tried my usual google to get answer which has got me closer but no luck :(

Upvotes: 0

Views: 2389

Answers (1)

LukeWarm
LukeWarm

Reputation: 27

So after some playing around I believe I have figured it out -

ClientTrigger = 

VAR Client = Sheet1[Client Name]
VAR Scheme = Sheet1[Scheme Name]
VAR HandlingTeam = Sheet1[Handling_Team_Name]
VAR GetTeam = CALCULATE (
                FIRSTNONBLANK ( 'Team Helper - DAX'[Team], 1 ),
                FILTER ( ALL ( 'Team Helper - DAX'), 'Team Helper - DAX'[Scheme] = Sheet1[Scheme Name])
                )
VAR Matching =  CALCULATE(COUNTROWS('Team Helper - DAX'), FILTER('Team Helper - DAX', 'Team Helper - DAX'[Team Helper]=Sheet1[Client Name])) > 0

RETURN
    SWITCH(
        TRUE(),
        Client = "Client 1" && Scheme = "Scheme 1" ,"Team 1",
        Client = "Client 2" && Scheme = "Scheme 2" ,"Team 2",
        Client = "Dummy" && HandlingTeam = "Team 3", "Team 3",
        Matching = TRUE(), GetTeam
    )

This is getting the same result as I was using the Excel formula and using the variables helped tidy up the overall look of the DAX expression.

Any clever folk want to glance their eyes over this and check there isn't some glaring errors? If not I think this is works for what I need :)

Upvotes: 0

Related Questions