Link tables with lists with Airtable

[romlal]

Hello everyone and apologies in advance if this is a duplicate. I am fairly new to PowerBI so I'll try to be as clear as possible:

My situation : In my DB (on Airtable) I have a first table called projects, which have several attributes such has customer, country, business lines, etc... customer, country and BLs are lists, because there can be 1 or many for each project.

 

My second table is called log, and each entry is bascically composed of a date, a project ID, and a quantity of time spent. No list here, only regular value.

My Third Table is called customer and contains for each customer several types of information such as the country of origin, type of customer etc...

 

My goal with all this : build an interactive dashboard that shows, for any selected interval of time, the time spent per project but also per business lines, per customer, etc... and I need to be able to split the time according to the number of items in the list. For example, if a project has 2 customers and I spent 100 days on it in 2021, I would like to have 50 days for each customer on my dashboar when I select the day 2021.

In addition, I need to see the split between the type of customers. for example, being able to read "in september 2021, I spent 73% of my time on VIP customers"

My Problem :  to do all this I need to build relationship based on list content : for example to link customers and projects, I need to find the customer ID in the column "customer" of my table project, which is a list.

and swcond issue: how do I split my time between the relevant number of BLs, Customers and countries ?

are intermediate tables my only way out ? do you have any recommendation? is my problem comprehensible ?

Thanks so much in advance for any support.

R.

[v-stephen-msft · Accepted solution]

Hi @romlal ,

Please refer to the blog, hope they can help you:

Back to Basics: Power BI Relationship Demystified - RADACAD

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

And maybe the star schema helps you:

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Star schema is a mature modeling approach widely adopted by relational data warehouses. It requires modelers to classify their model tables as either dimension or fact.

Best Regards,

Stephen Tao

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