Search code examples
powerbidaxdaxstudio

How can i convert sql query into dax query

I want to convert sql query into dax in power bi my table is:-

channel  advertiser yr
colors  pepsi   2019
colors  apple   2019
colors  pepsi   2018
colors  google  2018
colors  lux     2019

I have tried sql query to find new advertiser:-

select advertiser from ads
where yr=2019 and advertiser not in --new ads
(select advertiser from ads
where yr=2018 )

I want to convert this query into dax, how can i do it? I have tried this:

EVALUATE
CALCULATETABLE (VALUES(adv[advertiser]),
adv[channel]="colors",
adv[year]=2019 && VALUES(adv[advertiser]) 
not (VALUES(adv[advertiser]),
adv[year]=2018
))

Can anyone help? Thanks in advance..!!

Solution

  • This will return a table with the "new" advertiser

    EVALUATE
    CALCULATETABLE (
         VALUES(adv[advertiser])
        ,adv[channel]="colors"
        ,adv[year]=2019 
        , NOT adv[advertiser] IN CALCULATETABLE (
             VALUES(adv[advertiser])
            ,adv[year]=2018
        ))