Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
garythomannCoGC
Impactful Individual
Impactful Individual

How to rewrite summarize with addcolumns for my scenario

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 }

 

 

1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

(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
)

 



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



View solution in original post

2 REPLIES 2
garythomannCoGC
Impactful Individual
Impactful Individual

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.

ThxAlot
Super User
Super User

(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
)

 



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.