Forum Discussion

Kazu's avatar
Kazu
Icon for Helper II rankHelper II
1 year ago
Solved

Crossjoin 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 ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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]

     

Irwan's avatar
Irwan
Icon for Super User rankSuper User
1 year ago

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.