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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register 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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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