@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
