Converting Sql query to DAX

[Anonymous]

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!

[amitchandak · Accepted solution]

@Anonymous , 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]))))

[Anonymous]

Thank you @amitchandak  Exactly what I needed.