Forum Discussion
Convert SQL query to DAX
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.
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