Forum Discussion

SG2015's avatar
SG2015
Frequent Visitor
1 year ago
Solved

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 a...
  • mark_endicott's avatar
    mark_endicott
    1 year ago

    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. 

mark_endicott's avatar
mark_endicott
Super User
1 year ago

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.