Convert SQL query to DAX

[SG2015]

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]

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

[mark_endicott · Accepted solution]

@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. 

[SG2015]

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

[mark_endicott]

@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.