Many to one relationship duplicate value

[farooqk]

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
12-August-2022	Shopping	Grocery	10

My goal is to generate

Date	MasterCategory	SubCategory	Total Amount
2022-08-08 00:00:00	Food	Grocery	231
2022-08-08 00:00:00	Food	Restaurant	40
2022-08-08 00:00:00	Shopping	Electronics	30
2022-08-08 00:00:00	Shopping	Grocery	0
2022-08-09 00:00:00	Food	Grocery	10
2022-08-09 00:00:00	Food	Restaurant	0
2022-08-09 00:00:00	Shopping	Electronics	0
2022-08-09 00:00:00	Shopping	Grocery	0
2022-08-10 00:00:00	Food	Grocery	0
2022-08-10 00:00:00	Food	Restaurant	0
2022-08-10 00:00:00	Shopping	Electronics	5
2022-08-10 00:00:00	Shopping	Grocery	0
2022-08-11 00:00:00	Food	Grocery	0
2022-08-11 00:00:00	Food	Restaurant	10
2022-08-11 00:00:00	Shopping	Electronics	0
2022-08-11 00:00:00	Shopping	Grocery	0
2022-08-12 00:00:00	Food	Grocery	0
2022-08-12 00:00:00	Food	Restaurant	0
2022-08-12 00:00:00	Shopping	Electronics	0
2022-08-12 00:00:00	Shopping	Grocery	10

I have almost hit the goal but now I am running into this error within my linked solution:

 Most likely being caused by last line of the data.

I hope someone can provide a solution here.

[VahidDM · Accepted solution]

Hi @farooqk 

Please remove the relationship first, then use this DAX expression to create a new table:

New Table = 
VAR _Date =
    VALUES ( Data[Date] )
VAR _Cat =
    SUMMARIZE ( Data, Data[MasterCategory], Data[SubCategory] )
RETURN
    ADDCOLUMNS (
        CROSSJOIN ( _Date, _Cat ),
        "Amount", CALCULATE ( SUM ( Data[Amount] ), KEEPFILTERS ( Data[Date] ) ) + 0
    )

The output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[farooqk]

Thanks for the help!