cancel
Showing results for
Did you mean:
New Member

## CrossJoin off two columns combination with one column

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 =
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 */``````

1 ACCEPTED SOLUTION
Super User

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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Super User

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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!