Forum Discussion
Solved
Many to one relationship duplicate value
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.
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!!
2 Replies
- VahidDM
Super User
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!!
Thanks for the help!
