Forum Discussion

Kazu's avatar
Kazu
Helper II
10 months 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 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]
 
 
  • Jihwan_Kim's avatar
    Jihwan_Kim
    10 months 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]

     

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    10 months 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
    Super User
    10 months 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.

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    10 months ago

    Kazu 

    Replace your RETURN statement with this:

     

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

  • pbix1's avatar
    pbix1
    Resolver II
    10 months ago

    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)

  • v-echaithra's avatar
    v-echaithra
    Community Support
    10 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.

  • v-echaithra's avatar
    v-echaithra
    Community Support
    9 months ago

    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.