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]

     

v-echaithra's avatar
v-echaithra
Icon for Community Support rankCommunity Support
11 months ago

Hi Kazu ,

Thank you Kedar_Pande , Jihwan_Kim , Irwan , pbix1  for your inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.