Forum Discussion

jankriz's avatar
jankriz
New Member
4 years ago
Solved

ADDCOLUMNS row context in CALCULATE condition

Hi,

 

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

UserID    XYClicks

 

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's avatar
    AlB
    4 years ago

    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.

     

2 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.