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, 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 IDCampaign NameObjectiveImpressionsLink ClicksPurchasesKey Metric
242ATraffic5235352252
235BAwareness123561312356
626CSales523525858

 

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 IDObjectiveLink Clicks
242Traffic52
235Awareness1
626Sales2

 

Then another one:

Campaign IDObjectivePurchases
242Traffic2
235Awareness3
626Sales58

 

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,

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 )

     

     

3 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    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.