Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Manage an indirect Relationship between three tables

Hello, 

I have three tables, a sales table with : 

 

client 

product

date 

quantity

 

A client table which is the list of cients 

 

and my third table is very specific. For each group and during a special time interval a client is part of a group 

 

for example Client A is in group 1 for flour between september 2023 and december 2024  and in group 2 for water between august 2022 and january 2024. 

sales has a many ti one relationshhip with clients 

 

client has a one to many relationship to with groups 

 

because the clients appreas several times in sales and group I cannont connect directly the two tables. 

I want to calculate the sales by group. Can you help me ? 

Need Help
Tips and Tricks

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Read about how to handle "Slow Changing Dimensions"  in Power BI.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi All,
    Firstly  lbendlin thank you for your solution!
    And Anonymous ,Here are some ideas I have for you, we can rely on the grouping information in the Groups group to be assigned to each sales data, make sure that each sales data got a correct grouping, and then we are relying on the grouping, to do the calculation of the sales of each group.

    Sales groups(column) = 
 CALCULATE(
    MAX('Groups'[Group]),
    FILTER('Groups',
    'Sales'[Customer]='Groups'[Customer]&&
    'Sales'[Prouduct]='Groups'[Products]&&
    'Sales'[Date]>='Groups'[StartDate]&&'Sales'[Date]<='Groups'[EndDate]))
    Sales Total = CALCULATE(SUM('Sales'[Amount]),ALLEXCEPT('Sales','Sales'[Sales groups(column)]))

    If you still have questions, you can check the pbix file I uploaded, I hope it will help you, you can also provide your example data or pbix file, it can better help you solve the problem!

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Has your problem been solved after all this time, or has a new problem arisen, if there are any other questions on this issue, feel free to contact me and I'll get back to you as soon as I receive the message.

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

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