cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
6mon Regular Visitor
Regular Visitor

Summarize ignores context

Can you help me understand the difference between 

 

SUMX(
    SUMMARIZE(
        FACT_COST_RESOURCE_CATEGORY;
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_PROJECT];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_ACTIVITY];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_RESOURCE_CATEGORY];
        "COST PLANNED PROJECT";
        FIRSTNONBLANK(DIM_RESOURCE_CATEGORY[COST_EQUIPMENT_BUDGET];1)
        +
        FIRSTNONBLANK(DIM_RESOURCE_CATEGORY[COST_LABOR_BUDGET];1)
        +
        FIRSTNONBLANK(DIM_RESOURCE_CATEGORY[COST_UNIT_BUDGET];1)
    );
    [COST PLANNED PROJECT]
)

and

 

SUMX(
    SUMMARIZE(
        FACT_COST_RESOURCE_CATEGORY;
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_PROJECT];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_ACTIVITY];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_RESOURCE_CATEGORY]
    );
    FIRSTNONBLANK(DIM_RESOURCE_CATEGORY[COST_EQUIPMENT_BUDGET];1)
    +
    FIRSTNONBLANK(DIM_RESOURCE_CATEGORY[COST_LABOR_BUDGET];1)
    +
    FIRSTNONBLANK(DIM_RESOURCE_CATEGORY[COST_UNIT_BUDGET];1)
)

?

 

From what I can observe the second one doesn't care for the context.

 

I'm trying to optimize the top one and altough the second one is much faster (yay!), the results are wrong...

2 REPLIES 2
Super User
Super User

Re: Summarize ignores context

Have you tried using GROUPBY, which then gives you access to row context using CURRENTGROUP()

 

So something like:

GROUPBY(
	TABLE TO GROUP,
	GROUP BY COLUMN,
	GROUP BY COLUMN,
	ETC,
	"Name of New Column",
	SUMX (
		CURRENTGROUP(),
		Expression 
	)
)

Generally dont want to add columns in summarize, better to use ADDCOLUMNS and SUMMARIZE.  

6mon Regular Visitor
Regular Visitor

Re: Summarize ignores context

Thanks for the suggestion but FIRSTNONBLANK doesn't seem permitted in the GROUPBY functions...

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 150 members 1,570 guests
Please welcome our newest community members: