topic ADDCOLUMNS row context in CALCULATE condition in DAX Commands and Tips

ADDCOLUMNS row context in CALCULATE condition

jankriz — Mon, 06 Jun 2022 09:49:56 GMT

Hi,

I have a Table with picture pixels coordinates X and Y and number of clicks to each pixel from each user:

UserID

Clicks

Each user could make any number of clicks to any pixel.

Now I want to make summarized table where will be one row for each pixel and SUM of clicks. But not only from that pixel but form it and surrounding pixels which are defined by number Brush.

My idea was something like this code:

var Brush = 10 return ADDCOLUMNS(SUMMARIZE(Table,[X],[Y]),"SumClicks",CALCULATE(SUM(Table[Clicks]),ALL(Table[X],Table[Y]),AND(Table[X]<=[X]+Brush,Table[X]>=[X]-Brush)&&AND(Table[Y]<=[Y]+Brush,Table[Y]>=[Y]-Brush)))

But this doesn't work because [X] and [Y] form row context in summarized table can't be used in CALCULATE condition.

Have you any idea, how to make this work?

Thank you.

Re: ADDCOLUMNS row context in CALCULATE condition

AlB — Mon, 06 Jun 2022 11:29:47 GMT

Hi jankriz

Try this to create a new calculated table:

NewTable = VAR Brush = 10 RETURN ADDCOLUMNS ( SUMMARIZE ( Table1, Table1[X], Table1[Y] ), "SumClicks", CALCULATE ( SUM ( Table1[Clicks] ), ALL ( Table1[X], Table1[Y] ), AND ( Table1[X] <= EARLIER(Table1[X]) + Brush, Table1[X] >= EARLIER(Table1[X]) - Brush ) && AND ( Table1[Y] <= EARLIER(Table1[Y]) + Brush, Table1[Y] >= EARLIER(Table1[Y]) - Brush ) ) )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: ADDCOLUMNS row context in CALCULATE condition

jankriz — Mon, 06 Jun 2022 12:00:27 GMT

Thank you AlB , this works exactly how I need 😀