cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
yios Frequent Visitor
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ani1991 Member
Member

Re: Dax equivalent to sql select case when within group by

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)
)
image.png

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 2
Ani1991 Member
Member

Re: Dax equivalent to sql select case when within group by

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)
)
image.png

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 Frequent Visitor
Frequent Visitor

Re: Dax equivalent to sql select case when within group by

Thanks @Ani1991 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 389 members 3,510 guests
Please welcome our newest community members: