topic Re: How to rewrite summarize with addcolumns for my scenario in DAX Commands and Tips

How to rewrite summarize with addcolumns for my scenario

garythomannCoGC — Thu, 27 Apr 2023 05:09:28 GMT

Using summarize is not best practice. Best practices using SUMMARIZE and ADDCOLUMNS

The code below works both in daxstudio and in my dataset. The child table MBPL gets a new column added call CheckPoint Next.

Great. But reading the above article using summarize is not good. So how to refactor the below?

--CheckPoint Next = -- uncomment for pbi desktop EVALUATE -- comment out for pbi desktop but needed for daxstudio VAR FINANCIALYEARNEXT = SELECTEDVALUE ( 'x Reference Dates'[FINANCIALYEARNEXT] ) VAR MBM_Id = "148" -- testing --VAR MBM_Id = 'Measure Benefit Master'[Id] -- uncomment for pbi desktop VAR CHECKPOINTNEXT = SUMMARIZE ( FILTER ( VALUES ( 'Measure Benefit Progress List' ), VALUE ( 'Measure Benefit Progress List'[Financial Year Search] ) = VALUE ( FINANCIALYEARNEXT ) && 'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id ), [CheckPoint] ) ) RETURN { CHECKPOINTNEXT }

Re: How to rewrite summarize with addcolumns for my scenario

ThxAlot — Thu, 27 Apr 2023 09:42:43 GMT

(just a syntax error in your snipette of code, a ")" is redundant before RETURN)

EVALUATE -- comment out for pbi desktop but needed for daxstudio VAR FINANCIALYEARNEXT = SELECTEDVALUE( 'x Reference Dates'[FINANCIALYEARNEXT] ) VAR MBM_Id = "148" -- testing --VAR MBM_Id = 'Measure Benefit Master'[Id] -- uncomment for pbi desktop VAR CHECKPOINTNEXT = SUMMARIZE( FILTER( VALUES( 'Measure Benefit Progress List' ), VALUE( 'Measure Benefit Progress List'[Financial Year Search] ) = VALUE( FINANCIALYEARNEXT ) && 'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id ), [CheckPoint] ) RETURN { CHECKPOINTNEXT }

Nothing to change in your case since you did nothing but just extract distinct values of 'Measure Benefit Progress List'[CheckPoint].

From syntactic perspective, it equals to

VAR CHECKPOINTNEXT = CALCULATETABLE( VALUES( 'Measure Benefit Progress List'[CheckPoint] ), VALUE( 'Measure Benefit Progress List'[Financial Year Search] ) = VALUE( FINANCIALYEARNEXT ), 'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id )

Re: How to rewrite summarize with addcolumns for my scenario

garythomannCoGC — Fri, 28 Apr 2023 01:46:16 GMT

VAR CHECKPOINTNEXT = CALCULATETABLE( VALUES( 'Measure Benefit Progress List'[CheckPoint] ), VALUE( 'Measure Benefit Progress List'[Financial Year Search] ) = VALUE( FINANCIALYEARNEXT ) && 'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id )

Keeping as one filter by using '&&' gives better timing results.