SQL Statement to DAX

Hello Gurus

I am trying to convert the following queries to DAX, the queries being part of a Stored Proc. :. Is there a way I can convert these into equivalent DAX. Also is there a way I can store in temp tables, like the ones below in PBI?

Thanks in advance!

--Load into temp table #CURRENT_SRC

1) SELECT RPT_PRD_END_DT, MTH_CTGRY, BRWR_DSC, WAPD_NUM, WAPD_DEN
INTO #CURRENT_SRC
FROM (SELECT RPT_PRD_END_DT, MTH_CTGRY, BRWR_DSC, ROUND(WAPD_NUM ,2) AS WAPD_NUM, ROUND(WAPD_DEN ,2) AS WAPD_DEN
FROM [INDUSTRY_SUMMARY] AA
WHERE AA.RPT_PRD_END_DT between EOMONTH('30-APR-2020',-61) and '30-APR-2020') AA

--Load into STEP2

2) SELECT RPT_PRD_END_DT , BRWR_DSC

, CASE WHEN SUM(WAPD_den)=0 THEN NULL ELSE SUM(WAPD_NUM)/ SUM(WAPD_den) END AS WAPD_PCT

into #STEP2 FROM #CURRENT_SRC WHERE MTH_CTGRY='Q'

GROUP BY RPT_PRD_END_DT, BRWR_RPT_RIC_SMY_DSC

--Compute the Average now, for each summary desc:

3) SELECT BRWR_DSC, AVG(WAPD_PCT) as WAPD_PCT

FROM #STEP2 GROUP BY BRWR_DSC

Need Help

DataZoe
5 years ago
@scheri Okay, I was able to make the second two tables, but they have to be all separate DAX tables, not one like I was trying to do.

STEP 2 ?
SUMMARY COLUMNS (
CURRENT_SRC[RPT_PRD_END_DT],
CURRENT_SRC[BRWR_RPT_RIC_SMY_DSC],
FILTER ( CURRENT_SRC, CURRENT_SRC[MTH_CTGRY] to "M" ),
"WAPD_PCT",
YES (
SUM ( CURRENT_SRC[WAPD_DEN]) ) to 0,
BLANK (),
DIVIDE ( SUM ( CURRENT_SRC[WAPD_NUM] ), SUM ( CURRENT_SRC[WAPD_DEN] ) )
)
)

AVGTABLE ?
SUMMARY COLUMNS (
STEP2[BRWR_RPT_RIC_SMY_DSC],
"WAPD_PCT", AVERAGE ( STEP2[WAPD_PCT] )
)

And for @AllisonKennedy, it may not be the best way to get the job done. The ultimate goal you're looking for is that BRWR_DSC average of the WAPD_PCT it RPT_PRD_END_DT gives. This can be done in a single measure (I used M because there was no Q in the sample table):

Average WAPD_PCT per BRWR_DSC
AVERAGEX (
VALUES ( CURRENT_SRC[BRWR_RPT_RIC_SMY_DSC] ),
AVERAGEX (
CALCULATETABLE (
VALUES ( CURRENT_SRC[RPT_PRD_END_DT] ),
CURRENT_SRC[MTH_CTGRY] to "M"
),
DIVIDE (
ROUND ( SUM ( CURRENT_SRC[WAPD_NUM] ), 2 ),
ROUND ( SUM ( CURRENT_SRC[WAPD_DEN] ), 2 ),
BLANK ()
)
)
)

Please take a look at the attached PBIX.
Table DAX.pbix29 KB

13 Replies

AllisonKennedy
Community Champion
5 years ago
I would suggest doing step 1 in Power Query and loading the table as an actual table in the data model (Not temp table). Then you can do steps 2 and 3 using DAX once you have the table loaded into the model.

So Get data from SQL Server into Power Query, select the Industry_Summary table or view.
In the Home tab, click the Choose Columns button and untick select all, then tick
RPT_PRD_END_DT, MTH_CTGRY, BRWR_DSC, WAPD_NUM, WAPD_DEN
to select those columns.
Select WAPD_NUM, WAPD_DEN columns and Round them to two decimal places in transform tab.
Select RPT_PRD_END_DT column and filter it - you can do this using the advanced filtering in the dropdown.

Close and Apply changes to load into data model.

I'm not sure where this column has come from: BRWR_RPT_RIC_SMY_DSC
But you can create a matrix visual with your group by columns: RPT_PRD_END_DT, BRWR_RPT_RIC_SMY_DSC in Rows. Then create a measure that calculates:

WAPD_PCT = IF (SUM(WAPD_den)=0 ,BLANK(), DIVIDE(SUM(WAPD_NUM), SUM(WAPD_den) )

Finally, create another matrix visual with BRWR_DSC in columns and create a measure:

Average WAPD_PCT = AVERAGE(WAPD_PCT)

Hope that helps and makes sense. Let me know how you get on.
DataZoe
Microsoft Employee
5 years ago
Anonymous I have parsed out your queries though http://poorsql.com/ (there is also a plugin for Notepad++).

SELECT RPT_PRD_END_DT ,MTH_CTGRY ,BRWR_DSC ,WAPD_NUM ,WAPD_DEN INTO #CURRENT_SRC FROM ( SELECT RPT_PRD_END_DT ,MTH_CTGRY ,BRWR_DSC ,ROUND(WAPD_NUM, 2) AS WAPD_NUM ,ROUND(WAPD_DEN, 2) AS WAPD_DEN FROM [INDUSTRY_SUMMARY] AA WHERE AA.RPT_PRD_END_DT BETWEEN EOMONTH('30-APR-2020', - 61) AND '30-APR-2020' ) AA SELECT RPT_PRD_END_DT ,BRWR_DSC ,CASE WHEN SUM(WAPD_den) = 0 THEN NULL ELSE SUM(WAPD_NUM) / SUM(WAPD_den) END AS WAPD_PCT INTO #STEP2 FROM #CURRENT_SRC WHERE MTH_CTGRY = 'Q' GROUP BY RPT_PRD_END_DT ,BRWR_RPT_RIC_SMY_DSC --Compute the Average now, for each summary desc: SELECT BRWR_DSC ,AVG(WAPD_PCT) AS WAPD_PCT FROM #STEP2 GROUP BY BRWR_DSC

which makes it a little more readable 🙂

For your questions on temp tables, yes, you can create tables with DAX expressions using SUMMARIZE https://docs.microsoft.com/en-us/dax/summarize-function-dax and SUMMARIZECOLUMNS https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax to name the most used of the table DAX expressions outlined at https://docs.microsoft.com/en-us/dax/table-manipulation-functions-dax .

For example here is your first table:

  SUMMARIZECOLUMNS (
        'INDUSTRY_SUMMARY'[RPT_PRD_END_DT],
        'INDUSTRY_SUMMARY'[MTH_CTGRY],
        'INDUSTRY_SUMMARY'[BRWR_DSC],
        FILTER (
            'INDUSTRY_SUMMARY',
            DATESBETWEEN (
                'INDUSTRY_SUMMARY'[RPT_PRD_END_DT],
                EOMONTH ( DATE ( 2020, 4, 30 ), - 61 ),
                DATE ( 2020, 4, 30 )
            )
        ),
        "WAPD_NUM", ROUND ( SUM ( 'INDUSTRY_SUMMARY'[WAPD_NUM] ), 2 ),
        "WAPD_DEN", ROUND ( SUM ( 'INDUSTRY_SUMMARY'[WAPD_DEN] ), 2 )
    )

Edit: Here is the whole thing, you would add it in as a DAX table:

THEDAXVERSION =
VAR CURRENT_SRC =
    SUMMARIZECOLUMNS (
        'INDUSTRY_SUMMARY'[RPT_PRD_END_DT],
        'INDUSTRY_SUMMARY'[MTH_CTGRY],
        'INDUSTRY_SUMMARY'[BRWR_DSC],
        FILTER (
            'INDUSTRY_SUMMARY',
            DATESBETWEEN (
                'INDUSTRY_SUMMARY'[RPT_PRD_END_DT],
                EOMONTH ( DATE ( 2020, 4, 30 ), - 61 ),
                DATE ( 2020, 4, 30 )
            )
        ),
        "WAPD_NUM", ROUND ( SUM ( 'INDUSTRY_SUMMARY'[WAPD_NUM] ), 2 ),
        "WAPD_DEN", ROUND ( SUM ( 'INDUSTRY_SUMMARY'[WAPD_DEN] ), 2 )
    )
VAR STEP2 =
    SUMMARIZECOLUMNS (
        CURRENT_SRC[RPT_PRD_END_DT],
        CURRENT_SRC[BRWR_DSC],
        FILTER ( CURRENT_SRC, CURRENT_SRC[MTH_CTGRY] = "Q" ),
        "WAPD_PCT",
            IF (
                CURRENT_SRC[WAPD_DEN] = 0,
                BLANK (),
                DIVIDE ( CURRENT_SRC[WAPD_NUM], CURRENT_SRC[WAPD_DEN] )
            )
    )
VAR AVGTABLE =
    SUMMARIZECOLUMNS ( STEP2[BRWR_DSC], "WAPD_PCT", AVERAGE ( STEP2[WAPD_PCT] ) )
RETURN
    AVGTABLE
- Anonymous
  Not applicable
  5 years ago
  Thank you DataZoe. I am getting a syntax error, when I tried the first part creating the table:. Does it need a RETURN part? (All the ( and ) tally).
  The syntax for ')' is incorrect. (DAX(VAR current_src........
  This is the DAX table I try to create (Just the same, have different table name):
  
  CURRENT_SOURCE = VAR current_src=
  SUMMARIZECOLUMNS (
  'ENT_RSKRECESSION_INDUSTRY_SUMMARY'[RPT_PRD_END_DT],
  'ENT_RSKRECESSION_INDUSTRY_SUMMARY'[MTH_CTGRY],
  'ENT_RSKRECESSION_INDUSTRY_SUMMARY'[BRWR_RPT_RIC_SMY_DSC],
  FILTER (
  'ENT_RSKRECESSION_INDUSTRY_SUMMARY',
  DATESBETWEEN (
  'ENT_RSKRECESSION_INDUSTRY_SUMMARY'[RPT_PRD_END_DT],
  EOMONTH ( DATE ( 2020, 4, 30 ), - 61 ),
  DATE ( 2020, 4, 30 )
  )
  ),
  "WAPD_NUM", ROUND ( SUM ( 'ENT_RSKRECESSION_INDUSTRY_SUMMARY'[WAPD_NUM] ), 2 ),
  "WAPD_DEN", ROUND ( SUM ( 'ENT_RSKRECESSION_INDUSTRY_SUMMARY'[WAPD_DEN] ), 2 )
  )
  - DataZoe
    Microsoft Employee
    5 years ago
    Anonymous yes it would need the return part if you use it as a variable, simply adding the
    
    RETURN current_src
    
    at the end of that should work.

Forum Discussion

SQL Statement to DAX

13 Replies

Related content

SQL Statement opcion error

SQL statement in Warehouse

CASE Statement to DAX

Multiple IF statements in DAX

SQL CASE Statement to DAX Measure