1

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

Aditya Ranjan
  • 135
  • 1
  • 2
  • 11
  • CALCULATETABLE will create a totally new table (so you will have 2 different tables with adv), which gets calculated once when the data are loaded, I don't know which is your use-case, but maybe you just need a flag "New Adv" True/False in the advertiser table. What do you need to do with them? apply filters? – Giovanni Luisotto Mar 07 '20 at 11:48
  • I need to find new advertiser in dax as I found in sql query.How can I write dax query similar to sql query.? – Aditya Ranjan Mar 07 '20 at 14:56

1 Answers1

0

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
        ))
Giovanni Luisotto
  • 1,382
  • 7
  • 12
  • Thanks for your solution.. if we have multiple channels then how can I use group by in this?colors 2019 pepsi colors 2019 apple colors 2018 pepsi colors 2018 google sab 2018 coca sab 2019 apple sab 2018 kitkat sony 2018 kitkat 0 sony 2019 kitkat 0 sony 2018 coca 0 zee 2018 garlic 0 zee 2019 colgate 0 zee 2019 pepsi 0 – Aditya Ranjan Mar 08 '20 at 16:43
  • GROUP BY means that you have to compute something... which is not the case of this query. There are functions like GROUPBY and SUMMARIZE to create aggregate tables. I have no clue about your use case but this is not what you usually do in DAX. – Giovanni Luisotto Mar 08 '20 at 16:49
  • I have to find "new" advertiser channel wise .so how can I apply gruopby in above use case? – Aditya Ranjan Mar 08 '20 at 16:56