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 .

Super User

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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors