Hello All! I need some help about my LDM, I have a use case for example Season, Month and Order, I will try to explain my problem. So I have a relationship database. I have a table Season with (PK: id and Name)

[{1,Summer}, {2, Autumn}, {3,Winter}, {4, Spring}]

I have a table Month with (PK: id and Name)

[{1, January} ... {12, December}]

I have a Parent Child Table (SeasonMonths) with (PK SeasonId (FK), PK MonthId (FK)) [{1,6}, {1,7},{1,8},{1,9}, {2,9}....{4,6}] (So in this table you can see that a Month can be in 2 seasons And finally I have Order table (PK: OrderID, MonthId(FK), Amount, OrderDate, ....)

[{1,1,100$,2023-01-01}] So this first order is in first January and we assume that is in Winter

When I create LDM I would like in my Dashboard to filter by Season to see the month available and filter by month to see the total of order or amount by each month Currently when I do this without any manipulation the Season Filter, filter the Month perfectly but to Sum is not good because it keep all for examples If I have

January 5
February 3
March 4
April 10
May 15
June 6
July 7
August 1
September 20
October 30
November 40
December 50
Total : 291 order

And filter season for Winter I see Month (December, January, February, March) with All check the total still 291 order but In my mind the total does be (62). When I check December I see total 50, January total 5 and I I check January and December I see 55 but when I click all the total return to 291, what is my problem ?

Hi Mathieu, the filters in GoodData are applied in the direction of the arrow in the GoodData LDM and each standard arrow represents a 1-N relationship. In your case it seems you have a many-to-many relationship in your data (represented by the SeasonMonths table). This can also be represented in GoodData LDM. You just need to add a two-directional arrow between Month and SeasonMonths datasets. To do so, you can add the normal arrow from Month to SeasonMonths and then drag again from SeasonMonths back to Month. This way the Season will be filtering Orders because this way it goes by the arrows. Note that this many-to-many relationship can cause double-counting in case you would slice Orders by Seasons in case an order would fall to a month which is in multiple seasons. But filtering is safe and it will not cause double-counting.

Thank you, I will try it ! 🙂 I will let you know the results.