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

Converting Sql query to DAX

Hi All, 

 

I'm having some trouble converting this Sql query to DAX

 

select AGMST_CUSMST_ID, OrgSTS_ID, BookingType, month(orgstartdate) [Month], sum(CountAll) Volume, 
Count(distinct concat(id, OrgSTARTDATE)) Legs,
sum(RevenueAll) BaseRevenue, sum(RevenueEURALL) EURRevenue, sum(Price)
from PasF_BookingJourney b
where AGMST_CUSMST_ID = '525775'
and regdate < '2020-03-01'
and OrgSTARTDATE >= '2020-03-01'
and (CANXDATE >= '2020-03-01' or CANXDATE is null)
group by AGMST_CUSMST_ID, OrgSTS_ID, BookingType, month(orgstartdate)
order by Month
 
Any help would be great!
1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Kartiklal03 , For all the group bys you need to have columns in table, that will be added to the visual for the grouping.

With this new column 

Month = month([orgstartdate])

 

 

You need measures for aggregated values

Most of measure will created like this with appropriate aggregation

BaseRevenue =
calculate(Sum(PasF_BookingJourney[RevenueAll]), filter(PasF_BookingJourney, [AGMST_CUSMST_ID] = "525775" && [regdate] <= Date(2020,03,01) && [OrgSTARTDATE] >= date(2020,03,01)
&& ([CANXDATE] >= date(2020,03,01) || isblank([CANXDATE]))))

 

 

This will be bit different
Legs =
calculate(countows(summarize(PasF_BookingJourney,[ID],[OrgSTARTDATE])), filter(PasF_BookingJourney, [AGMST_CUSMST_ID] = "525775" && [regdate] <= Date(2020,03,01) && [OrgSTARTDATE] >= date(2020,03,01)
&& ([CANXDATE] >= date(2020,03,01) || isblank([CANXDATE]))))

 

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@Kartiklal03 , For all the group bys you need to have columns in table, that will be added to the visual for the grouping.

With this new column 

Month = month([orgstartdate])

 

 

You need measures for aggregated values

Most of measure will created like this with appropriate aggregation

BaseRevenue =
calculate(Sum(PasF_BookingJourney[RevenueAll]), filter(PasF_BookingJourney, [AGMST_CUSMST_ID] = "525775" && [regdate] <= Date(2020,03,01) && [OrgSTARTDATE] >= date(2020,03,01)
&& ([CANXDATE] >= date(2020,03,01) || isblank([CANXDATE]))))

 

 

This will be bit different
Legs =
calculate(countows(summarize(PasF_BookingJourney,[ID],[OrgSTARTDATE])), filter(PasF_BookingJourney, [AGMST_CUSMST_ID] = "525775" && [regdate] <= Date(2020,03,01) && [OrgSTARTDATE] >= date(2020,03,01)
&& ([CANXDATE] >= date(2020,03,01) || isblank([CANXDATE]))))

 

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank you @amitchandak  Exactly what I needed. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors