IF Clause, get value from another table

[Anonymous]

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, and for each objective we have one metric to measure the campaign's results. So, for example, if the campaign objective was Traffic, then the key metric would be "Link Clicks". If the objective was Sales, then we would pick "Purchases". I would do this very easily by creating a conditional table on Power Query = if objective = X, then value = Y.

So basically, before:

Campaign ID	Campaign Name	Objective	Impressions	Link Clicks	Purchases	Key Metric
242	A	Traffic	52353	52	2	52
235	B	Awareness	12356	1	3	12356
626	C	Sales	5235	2	58	58

However, we are changing the structure of our dataset, and now I have several different tables - one for each objective. So one for Traffic, one for Impressions, one for Leads, etc. Finally, I have one tabled called "CAMPAIGN INDEX" with the Campaign ID and the Campaign Objective for every single campaign.

I've created a many-to-many relationship between the tables, using the Campaign ID as the common field:

Campaign ID	Objective	Link Clicks
242	Traffic	52
235	Awareness	1
626	Sales	2

Then another one:

Campaign ID	Objective	Purchases
242	Traffic	2
235	Awareness	3
626	Sales	58

Now what I wanted is to create a column in the CAMPAIGN INDEX table, where it will basically get the most important metric according to each objective. So in other words:

If Objective = Traffic, get LINK CLICKS from the LINK CLICKS table

If Objetive = Sales, get PURCHASES from the SALES table, and etc.

I am not that experienced on DAX and I am much more comfortable with PowerQuery. Can anybody help me?

Thank you,

[smpa01 · Accepted solution]

@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]

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]

@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.