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]

[v-echaithra]

Hi @Kazu ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

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.

[pbix1]

Hi @Kazu

I have done something similar to this, but with the summarisations done before the calculations. Obviously replace the tables, calculations etc. with your own. However, most of it will work using the generic naming. Hopefully this may help you. The DAX below is to create a calculated table.

PS - if anyone reading this has any ideas on how to improve this or make it more efficient, I would be interested to know. Thanks.

Table =
VAR SP01 = SELECTCOLUMNS('Table_A',"Week_Code_A",'Table_A'[Week_Code_A],"Data_A",'Table_A'[Data_Value_A])
VAR SP02 = SELECTCOLUMNS(FILTER('Table_B','Table_B'[Flag_B]="B"),"Week_Code_B", 'Table_B'[Week_Code_B],"Data_B",CONVERT('Table_B'[Data_Value_B],DOUBLE))
VAR SP03 = SELECTCOLUMNS(FILTER('Table_C','Table_C'[Flag_C]="C"),"Week_Code_C", 'Table_C'[Week_Code_C],"Data_C",CONVERT('Table_C'[Data_Value_C],DOUBLE))
VAR SP04 = GROUPBY(SP01,[Week_Code_A],"Total_A",SUMX(CURRENTGROUP(),[Data_A]))
VAR SP05 = GROUPBY(SP02,[Week_Code_B],"Total_B",SUMX(CURRENTGROUP(),[Data_B]))
VAR SP06 = GROUPBY(SP03,[Week_Code_C],"Total_C",SUMX(CURRENTGROUP(),[Data_C]))
VAR SP07 = CROSSJOIN(SP04,SP05,SP06)
VAR SP08 = FILTER(SP07,AND([Week_Code_A]=[Week_Code_B],[Week_Code_B]=[Week_Code_C]))
VAR SP09 = ADDCOLUMNS(SP08,"Calculation",IF([Total_A]=0,0,DIVIDE([Total_A]-[Total_B]-[Total_C],[Total_A])))
VAR SP10 = SELECTCOLUMNS(SP09,"Week_Code",[Week_Code_A],"Result_A",[Total_A],"Result_B",[Total_B],"Result_C",[Total_C],"Percentage",FIXED([Calculation],10))

RETURN
CALCULATETABLE(SP10)

[Kedar_Pande]

@Kazu 

Replace your RETURN statement with this:

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

[Jihwan_Kim · Accepted solution]

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]

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.