Forum Discussion

ErikHolmberg's avatar
ErikHolmberg
Frequent Visitor
1 year ago

Help with managing relationships

I'm new to Power BI and I'm having trouble managing relationships between tables to get them to work correctly together. These are the tables and data structure I'm working with.

 

Servicelist
Client Team Name
Client Team ID
Client Name
Client ID
Product
Start date
End date

 

salesinvoice
Client Team Name
Client Team ID
Client Name
Client ID
Amount
Date

 

Budget
Client Team Name
Client Team ID
Month
Budget amount
Budget # services

 

I've also created a separe date table. In each table the IDs appear multiple times which creates a many-to-many dilemma when I try creating relationships.

 

The output I would like is to be able to track actual performance from salesinvoice(for sum of amount) and servicelist(for number of services sold) tables against the budget from the budget file. I would like to do this based per Client Team and month from a slicer. 

 

I've tried what feels like virtually all possible different relationships. I've also tried making a table like in this example but couldn't get it to work. Any ideas how to get this to work proberly? 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ErikHolmberg ,

     

    Thanks to lbendlin  and DataInsights  for their quick replies and advice. I have some other ideas to add:

    (1) We can create a date table and a Client_Team_ID table.

    Date = SUMMARIZE(ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)) ,"MonthColumn",FORMAT([Date],"yyyy-mm") ),[MonthColumn])
    Client_Team_ID = VALUES('Budget'[Client Team ID])

    (2) Create a column on Budget table.

    MonthColumn = FORMAT([Month],"yyyy-mm")

    (3) Creating Model Relationships.

    (4) Create measures.

    budget_amount = SUM('Budget'[Budget amount]) 
    budget_services = SUM('Budget'[Budget # services])
    count_product = 
var _month=FORMAT(MAX('Servicelist'[Start date]),"yyyy-mm")
RETURN COUNTROWS(FILTER(ALLSELECTED('Servicelist'),[Client Team ID]=MAX('Client_Team_ID'[Client Team ID]) && _month= MAX('Date'[MonthColumn])))
    sale_amount = 
var _month=FORMAT(MAX('salesinvoice'[Date]),"yyyy-mm")
RETURN
CALCULATE(SUM('salesinvoice'[Amount]),FILTER(ALLSELECTED('salesinvoice'),[Client Team ID]=MAX('Client_Team_ID'[Client Team ID]) && _month =MAX('Date'[MonthColumn])))

    (5) Then the result is as follows.

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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

    • ErikHolmberg's avatar
      ErikHolmberg
      Frequent Visitor
      1 year ago

      Thanks, this helps a lot!

      I followed your steps and tested the table visualization. There are a few things I don't fully get working though. 

      • For sale_amount and count_product it only shows for the last month and not any values for prevouis months
      • When I use other visuals(tested with gauge and bar chart) it shows the sale_amount for the Client Team ID with the highest value rather than the sum of the selcted teams. But at the same time it shows the budget_amount only for the selcted teams - this is how I would prefer it to work.

       

      I'd be happy to provide some data, do you want to see all talbes anonymized or anything certain?

       

      Thanks for the help!

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi ErikHolmberg ,

         

        We can create month columns on the servicelist and salesinvoice tables.

        MonthColumn = FORMAT([Date],"yyyy-mm")
        MonthColumn = FORMAT([Start date],"yyyy-mm")

        Then create model relationships.

        Update the measures.

        count_product = 
COUNTROWS('Servicelist')
        sale_amount = 
SUM('salesinvoice'[Amount])

        If this does not solve your problem, please provide the pbix file (please note that private data is removed and the file is publicly accessible)

        Refer to:

        How to provide sample data in the Power BI Forum

        How to Get Your Question Answered Quickly

         

        Best Regards,

        Neeko Tang

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

         

  • DataInsights's avatar
    DataInsights
    Super User
    1 year ago

    ErikHolmberg,

     

    I would start by creating a Client dimension table with one row per Client ID. The other fields Client Name, Client Team ID, and Client Team Name are attributes of Client ID, so these would be fields in the Client dimension. Then create one-to-many relationships on Client ID:

     

    Client dimension --> Servicelist

    Client dimension --> salesinvoice

     

    Since the Client dimension contains Client Name, Client Team ID, and Client Team Name, you can remove these fields from Servicelist and salesinvoice.

     

    To create the relationship between the Client dimension and Budget, you'll need a bridge table consisting of distinct Client Team ID. Create one-to-many relationships on Client Team ID:

     

    Bridge table --> Client dimension

    Bridge table --> Budget

     

    Then use the CROSSFILTER function (as described in the linked post) to filter budget measures by Client Team ID or Client Team Name (in the Client dimension table).

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Think about the basic concepts

     

    Dimensions - something you filter by (Date, Client, Product)

    Facts - something you calculate (Amount, Budget Amount).

     

    These are joined in a star schema (dimensions controlling facts), ideally in 1:* single direction relationships.

     

    See if you can refactor your tables to remove all the redundant columns, and focus on just the ones that matter.