cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

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

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors