ADDCOLUMNS row context in CALCULATE condition

[jankriz]

Hi,

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

UserID

X

Y

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.

[AlB · Accepted solution]

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.

[jankriz]

Thank you @AlB , this works exactly how I need 😀