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 )

     

     

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.