Forum Discussion
Convert SQL query to DAX
Hi community,
can someone pls help me out with this sql query to be converted in dax?
All data is in the same table. The sql speaks for itself but goal is to aggregate specific positions by id and the booking_date of revenue and get corresponding costs.
Thank you for any input on this.
best regards,
sinan
with rev as
(
select
id,
max(case when pos in ('A', 'B') then BOOKING_DATE end) as booking_date,
sum(book_amount) as revenue
from
table
where
cat = 'NEW' and
pos in ('A','B','C','D','E','F')
group by id
),
cost as
(
select
id
sum(book_amount) as cost
from
table
where
cat = 'NEW' and
pos in ('X')
group by id
)
select
a.id
a.booking_date,
a.revenue,
b.cost
from
rev a inner join
cost b on a.id = b.id
;
SG2015 - I'm not sure you understand how calculated tables work. They are not computed "on the fly" as people interact with the data in a report. They are calculated at the point of a refresh, but after all of the transformations are complete in Power Query. The means they are less efficient for the data model and not subjected to the same compression algorithms that tables created in Power Query are, you would actually be better off creating an aggregated table in Power Query.
However, if you would like to make this as a calculated table, here is the DAX:
CALCULATETABLE ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[BOOKING_DATE], "Revenue", CALCULATE ( SUM ( 'Table'[BOOK_AMOUNT] ), KEEPFILTERS ( 'Table'[POS] IN { "A", "B", "C", "D", "E", "F" } ) ), "Cost", CALCULATE ( SUM ( 'Table'[BOOK_AMOUNT] ), KEEPFILTERS ( 'Table'[POS] = "X" ) ) ), 'Table'[Column1] = "NEW" )If this works, please mark it as the solution for the visibility of others.
4 Replies
SG2015 - You only need to create measures for the two calculations, the visual you build will do the rest, the DAX for these measures is below:
Revenue = CALCULATE ( Table[book_amount], KEEPFILTERS ( Table[cat] = "NEW" ), KEEPFILTERS ( Table[pos] IN { "A", "B", "C", "D", "E", "F" } ) ) Cost = CALCULATE ( Table[book_amount], KEEPFILTERS ( Table[cat] = "NEW" ), KEEPFILTERS ( Table[pos] = "X" ) )Create these in two different measures and place them against the appropriate columns from your table.
If this works, please accept as a solution - it helps with visibility for others with the same challenge.
Thank you Mark.
Good idea with the measures, but I am afraid, this is not the output I need.
I'm rather looking for a table-output than a measure-output. Something like the following, although I still couldn't figure out the cost part (red color):
EVALUATE
SUMMARIZECOLUMNS (
table[ID],
TREATAS({"A", "B", "C", "D", "E","F"}, table[POS]),
TREATAS({"NEW"}, table[CAT]),
"Booking_date", MAX(table[BOOKING_DATE]),
"Revenue", SUM(table[BOOK_AMOUNT]),
"Cost" , CALCULATE(sum(table[BOOK_AMOUNT]), FILTER(table, table[cat] = "NEW"), FILTER(table, table[POS] = "X"))
)The code in red is just for demonstration. I know that calculate and filter does not work in this context. But how would I make it work so it filters independently from revenue-data? Just like in the SQL? Can I use two summarizecolumns-functions - one for revenue and one for cost - and then join/merge them together? Is the function SUMMARIZECOLUMNS the right one or is it better done with CALCULATETABLE? Am really struggling with the DAX-Syntax for problems like this.
I basically want to create a table on the fly. The underlying data is huge and I don't want to create an aggregated table with power query. The fields ID and BOOKING_DATE will be joined with dimensions afterwards.
Main Purpose:
Cost data has booking_dates which are different from booking_dates of revenue data. With the summarized table I want 1.) an aggreagtion of the huge underlying data and 2.) booking_date to behave as if costs have been realized on same date as revenue, to use only one date slicer afterwards. Output should be like this:
ID BOOKING_DATE REVENUE COST 1 2024-01-01 10 5 2 2024-01-02 20 15 3 2024-01-03 30 25 4 2024-01-04 40 45 5 2024-01-05 50 20 Thanks. I appreciate your help.
Best. Sinan
SG2015 - I'm not sure you understand how calculated tables work. They are not computed "on the fly" as people interact with the data in a report. They are calculated at the point of a refresh, but after all of the transformations are complete in Power Query. The means they are less efficient for the data model and not subjected to the same compression algorithms that tables created in Power Query are, you would actually be better off creating an aggregated table in Power Query.
However, if you would like to make this as a calculated table, here is the DAX:
CALCULATETABLE ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[BOOKING_DATE], "Revenue", CALCULATE ( SUM ( 'Table'[BOOK_AMOUNT] ), KEEPFILTERS ( 'Table'[POS] IN { "A", "B", "C", "D", "E", "F" } ) ), "Cost", CALCULATE ( SUM ( 'Table'[BOOK_AMOUNT] ), KEEPFILTERS ( 'Table'[POS] = "X" ) ) ), 'Table'[Column1] = "NEW" )If this works, please mark it as the solution for the visibility of others.
Thank you Mark. I will give it another thought.
But for now, this is exactly what I would need. Thank you very much.
Best, Simon
