Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

IF Clause, get value from another table

Hello, For years, I have worked with one big table containing all sorts of information from my marketing campaigns: impressions, link clicks, sales, etc. Every campaign has a different objective, an...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  you can write a measure like this

    Measure =
VAR _obj =
    MAX ( 'CAMPAIGN INDEX'[Objective] )
VAR _LinkClicks =
    CALCULATE (
        MAX ( 'LINK CLICKS'[Link Clicks] ),
        FILTER (
            'LINK CLICKS',
            ( 'LINK CLICKS'[Campaign ID], 'LINK CLICKS'[Objective] )
                IN SUMMARIZE (
                    'CAMPAIGN INDEX',
                    'CAMPAIGN INDEX'[Campaign ID],
                    'CAMPAIGN INDEX'[Objective]
                )
        )
    )
VAR _Purchase =
    CALCULATE (
        MAX ( PURCHASES[Purchases] ),
        FILTER (
            PURCHASES,
            ( PURCHASES[Campaign ID], PURCHASES[Objective] )
                IN SUMMARIZE (
                    'CAMPAIGN INDEX',
                    'CAMPAIGN INDEX'[Campaign ID],
                    'CAMPAIGN INDEX'[Objective]
                )
        )
    )
RETURN
    SWITCH ( TRUE (), _obj = "Traffic", _LinkClicks, _obj = "Sales", _Purchase )

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I've had a few tests and it seems about right! (My table actually have some 5 or 6 different objectives, and I need to test with the rest).

 

Could I just kindly ask you to explain to me the logic behind it (or provide me further reading)? I understand we created 3 variables, one for the objective and one for each objective, and that we are returning the key metric for each objective. But I don't get why we are using the MAX function when defining the key metrics on the objectives' variables.

 

Sorry if this seems obvious, it's just that as I said I am still a bit unfamiliar with DAX.

 

Thanks so much!

smpa01's avatar
smpa01
Community Champion
4 years ago

Anonymous  recommended Reading 

 

The high levvel logic of this measure is, 

 

I am building seperate lookups such as

VAR _LinkClicks
VAR _Purchase

 

You can build the rest following the syntax I used.

 

The DAX syntax I used in this lookup is IN. I also could have used TREATAS,CONTAINS,INTERSECT to return the same. But let's focus on IN for the time being.

 

Within IN syntanx I am aksing DAX to return the  MAX of lookuptable value such as

MAX ( 'LINK CLICKS'[Link Clicks] )

 

while look up the  following combination 

'CAMPAIGN INDEX'[Campaign ID],
                    'CAMPAIGN INDEX'[Objective]

 

in 

'LINK CLICKS'[Campaign ID], 'LINK CLICKS'[Objective]

 

IN Syntax uses a row reference and you need to provide the

<LookupTable>,<LookupRowCombination>IN <TARGET Table>

 

which pans out as this

        FILTER (
            'LINK CLICKS',
            ( 'LINK CLICKS'[Campaign ID], 'LINK CLICKS'[Objective] )
                IN SUMMARIZE (
                    'CAMPAIGN INDEX',
                    'CAMPAIGN INDEX'[Campaign ID],
                    'CAMPAIGN INDEX'[Objective]
                )
        )

 

Lastly, used a simple SWITCH where you define which lookup value to return based on the condition specified by you.

 

Please don't forget to accept the answer.