Crossjoin and then summarize

[Kazu]

I want to 1) crossjoin two tables, 2)do some calculation, and finally 3) summarize/aggregate.

I was able to do 1 and 2 but cannot the summary at 3. When I tried to SUMMARIZE or SUMMARIZECOLUMNS, it only allows me to summarize the source table but not the temporary tables. 

Here is the DAX code. BaseTable is a simple table with only two columns Index and Value. What I want to get is Index and SUM(Value3).

EVALUATE

VAR SHIFT  = 2

VAR REPEAT = 3

// Create a temporary table crossjoining Base Table with Base Table

VAR TBL1 = ALL(BaseTable)

VAR TBL2 = SELECTCOLUMNS(

    ALL(BaseTable),

    "Index2", BaseTable[Index],

    "Value2", BaseTable[Value]

)

VAR TBL3 = ADDCOLUMNS(

    CROSSJOIN(TBL1, TBL2),

    "Value3", [Value2] * IF([Index2] >= BaseTable[Index]-SHIFT*(REPEAT-1) && [Index2] <= BaseTable[Index] && MOD(BaseTable[Index],SHIFT) = MOD([Index2], SHIFT), 1, BLANK())

)

RETURN TBL3

// RETURN SUMMARIZE(TBL3..... <- this is not allowed.

ORDER BY BaseTable[Index], [Index2]

[Irwan]

hello @Kazu 

not sure how your data looks like, but i think you should be able to do this in new table.

however if you do this in measure, you need to aggregate your summarize because measure only accect single/scalar value (since summarize outcome is a form of table, measure can not have table as outcome).

Hope this will help.

Thank you.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, and I cannot know what is the expected result looks like.

Please check the below picture and the attached pbix file, if I missed something.

EVALUATE
VAR SHIFT  = 2
VAR REPEAT = 3

// Create a temporary table crossjoining Base Table with Base Table
VAR TBL1 = ALL(BaseTable)
VAR TBL2 = SELECTCOLUMNS(
    ALL(BaseTable),
    "Index2", BaseTable[Index],
    "Value2", BaseTable[Value]
)
VAR TBL3 = ADDCOLUMNS(
    CROSSJOIN(TBL1, TBL2),
    "Value3", [Value2] * IF([Index2] >= BaseTable[Index]-SHIFT*(REPEAT-1) && [Index2] <= BaseTable[Index] && MOD(BaseTable[Index],SHIFT) = MOD([Index2], SHIFT), 1, BLANK())
)
RETURN 
SUMMARIZE(
ADDCOLUMNS(TBL3, "@Value3Sum", sumx(FILTER(TBL3, BaseTable[Index] = EARLIER(BaseTable[Index])), [Value3])), BaseTable[Index], [@Value3Sum])
ORDER BY BaseTable[Index]

[Kedar_Pande]

@Kazu 

Replace your RETURN statement with this:

RETURN
GROUPBY(
TBL3,
BaseTable[Index],
"Sum of Value3", SUMX(CURRENTGROUP(), [Value3])
)