CrossJoin off two columns combination with one column

farooqk — Fri, 07 Oct 2022 19:31:13 GMT

I have this data:

Date	MasterCategory	SubCategory	Amount
8-August-2022	Food	Grocery	200
8-August-2022	Food	Grocery	31
8-August-2022	Food	Restaurant	40
8-August-2022	Shopping	Electronics	30
9-August-2022	Food	Grocery	10
10-August-2022	Shopping	Electronics	5
11-August-2022	Food	Restaurant	10

my goal is to generate:

Date	MasterCategory	SubCategory	Amount
8-August-2022	Food	Grocery	231
8-August-2022	Food	Restaurant	40
8-August-2022	Shopping	Electronics	30
9-August-2022	Food	Grocery	10
9-August-2022	Food	Restaurant	0
9-August-2022	Shopping	Electronics	0
10-August-2022	Food	Grocery	0
10-August-2022	Food	Restaurant	0
10-August-2022	Shopping	Electronics	5
11-August-2022	Food	Grocery	0
11-August-2022	Food	Restaurant	10
11-August-2022	Shopping	Electronics	0

Please do note that amount value in first row of above column is sum by master,sub and date.

I have got this partially worked out with below code but it is missing the mastercategory column, hopefully someone can help me:

/* START QUERY BUILDER */ DEFINE MEASURE 'myBank'[Sum_Measure] = VAR _A = ADDCOLUMNS ( CROSSJOIN ( VALUES ( 'myBank'[SubCategory] ), VALUES ( 'myBank'[Date] ) ), "M1", CALCULATE ( SUM ( 'myBank'[Amount] ), FILTER ( 'myBank', 'myBank'[SubCategory] = EARLIER ( [SubCategory] ) && 'myBank'[Date] = EARLIER ( [Date] ) ) ) + 0 ) VAR _B = SUMX ( _A, [M1] ) RETURN _B EVALUATE SUMMARIZECOLUMNS( myBank[SubCategory], myBank[Date], "Sum Col", [Sum_Measure] ) ORDER BY myBank[Date] /* END QUERY BUILDER */

Re: CrossJoin off two columns combination with one column

amitchandak — Sat, 08 Oct 2022 03:22:54 GMT

farooqk , I think a simple measure with dimension should do if needed add a date table too .

Please find the file attached

topic Re: CrossJoin off two columns combination with one column in DAX Commands and Tips

CrossJoin off two columns combination with one column

Re: CrossJoin off two columns combination with one column