Forum Discussion

yios's avatar
yios
Helper I
7 years ago
Solved

Dax equivalent to sql select case when within group by

Hello, I am trying to write with DAX the following SQL query 

I know that case when equivalent is switch statement and group by is summarize but i can't get it work.

 

select
video,
sum(case when type=1 then 1 else 0 end) as upvote,
sum(case when type=0 then 1 else 0 end) as downvote
from votes
group by video


Does anybody know how to do it?

Need Help
  • Ani1991's avatar
    Ani1991
    7 years ago

    Hi yios 

     

    You can try using something this below,

    Click on New Table under Modelling tab and use the below expression. Replace the column names according to the one present in your dataset.

     

    Table = SUMMARIZE(Dummy,Dummy[Project ID],"Video",Dummy[Project ID],
    "Upvote",SWITCH(MAX(Dummy[Planned hours]),10,1,0),
    "Downvote",SWITCH(MAX(Dummy[Planned hours]), 20,2,0)
    )

    A new dataset would be created. Drag those columns in a table visual. You will get the output as the adjoining image.

     

     

     

    Thanks,

    Ani

2 Replies

  • Ani1991's avatar
    Ani1991
    Resolver III
    7 years ago

    Hi yios 

     

    You can try using something this below,

    Click on New Table under Modelling tab and use the below expression. Replace the column names according to the one present in your dataset.

     

    Table = SUMMARIZE(Dummy,Dummy[Project ID],"Video",Dummy[Project ID],
    "Upvote",SWITCH(MAX(Dummy[Planned hours]),10,1,0),
    "Downvote",SWITCH(MAX(Dummy[Planned hours]), 20,2,0)
    )

    A new dataset would be created. Drag those columns in a table visual. You will get the output as the adjoining image.

     

     

     

    Thanks,

    Ani