Forum Discussion
Kazu
Helper II
Helper IICrossjoin and then summarize
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 ...
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]
Jihwan_Kim
Super User
Super UserHi,
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]