One to many relationship

I’m pulling my hair out! Can you take a look at the attached sample and help me (please) understand why average age is not calculating at the product level. The age is the same number.

In my model, I have a one household code (customer) to many household codes (customers) relationships.

I could choose to keep the file flat, however, its end size (3.2MM records) will be very slow to run. To that end, I’m trying to split the file into look-up and fact tables. One dimension is demographics.

I created a unique household code for each customer.

Within the sales fact table, there are multiple household codes because a customer will have multiple products.

I then made a demographics look-up table containing a single household row. There is only one demographic piece of data per household per demographic type.

Sample File.pbix (213.1 KB)

Hi @rizzotony

You are using sum on the age detail in the sales table


I’m not sure why you would sum the detail information.

If you use the Proddesc in the Demographic and ave age calculation(comes from Demographic table) in the key measure table then i think you will get the right answer.

I have attached the file for your review.

thanks
Keith
Sample File kstest.pbix (213.3 KB)

2 Likes

Thank you very much Keith,

I think what I was trying to do was use numbers (look-up table) to filter the words (fact table). Your correct solution is the opposite.

So from a learning perspective, how do you explain the difference between a look-up table and a fact table? I’m asking because I’m really trying to understand the most basic of PBI mechanics.

2 Likes

Hey @rizzotony, hope you’re well :grinning:

@BrianJ shared this book recommendation yesterday, which I’ll also buy shortly too.

1 Like

Hi @rizzotony

Look up tables are one occurrence not many. I would change your calculation for your average to read your fact table and also change your age to detail not to summarize in your fact table.

@DavieJoe makes a good suggestion too.

Maybe others will make a suggestions too.

sorry I have a hard time putting it in writing :frowning:

I’m learning too

thanks
Keith

2 Likes

Fact Tables will hold information on things that happened like Book A was sold to Customer 0001, facts. Lookup Tables (Dimension Tables) will have descriptive information around Customers for example such as address, gender, birthday.

3 Likes

@rizzotony ,

@Keith and @DavieJoe are both spot on. I’ll throw in another way of thinking about it that I find helpful in making the distinction between fact and lookup/dimension tables. Fact tables represent verbs - e.g., hiring people, manufacturing widgets, submitting trouble tickets, purchasing goods, etc. Dimension tables represent nouns associated with those actions – e.g., dates, employees, products, customers, countries, etc.

Fact tables change constantly – often daily, sometimes like the most recent Data Challenge about emergency services and ambulances, by the minute or second. Dimension tables can also change (employees get promoted to new positions, customers change addresses, countries get renamed, etc.), but these happen on a much less frequent basis than fact table changes.

Typically, fact tables are long and thin (many rows – in some cases perhaps millions or billions, few columns), while on the other hand dimension tables typically are shorter and wider (more columns, far fewer rows).

Finally, going back to your original problem and Keith’s solution, typically you aggregate (e.g., SUM, AVERAGE, MAX, etc.) on fact tables and filter on dimension tables.

I would say that learning proper data modeling skills is the most essential thing to learn in Power BI, because if you get the data model wrong you are sunk right out of the gate – no matter how skillful you may be with DAX, your calculations and visuals may not produce accurate results.

Your timing is impeccable though - the current Power BI Accelerator week #2 problem is focused on exactly these issues. If before Wednesday evening ET you have an hour to work through the problem, and then attend the live solution discussion (or watch it on video later), @sam.mckay and I are going to walk through the specific example of how to build a proper data model from scratch. You don’t have to have done the week #1 problem – you can just jump in directly to week #2. I think it wii really help you understand these concepts.

I hope this is helpful.

– Brian

3 Likes

@keith @DavieJoe and Brian - Thank you very much. I truly appreciate your time and insight. This is a great place to learn. I hope you take pride and joy in realizing your responses have such a positive and lasting impact (not to mention Amazon book sales)!

@sam.mckay you have a great company.

Tony

3 Likes

Thanks for this kind words - your message really made my day. I have learned so much from so many amazing members of this community, and do get a lot of joy out of trying to “pay that forward” in small part by helping others here.

  • Brian
2 Likes