Dax equivalent to sql select case when within group by

[yios]

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?

[Ani1991 · Accepted solution]

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

[yios]

Thanks @Ani1991