Forum Discussion
Complex modeling with 4 tables. need help!
So far you don't have a data model yet, just a bunch of tables somehow wired together. In a data model you have dimension tables (stuff you filter by) and fact tables (stuff you calculate). Dimensions control facts in a 1:* single direction relationship. Can you identify the type of your tables?
Also, all 4 are fact tables, but i would like the category fields from each table to use across all tables.
Power BI is the wrong tool for that. If you need an associative model, use Qlik.
In Power BI you will have the best success with a star schema data model. Normalize your data so you have dimensions for Date, Account, Industry etc, and facts that describe actual transactions.
so lets say that I am trying to create a dims table based on the table below, where an account can be in multiple categories.
Do i need to pick the top 1 based on revenue, or should I pivot the industry column so that there is a unique column for each category?
Account ID Account ID Account Name Month Industry revenue 2324 2324 acme 1 1/1/2023 apparel 232 2324 2324 acme 1 2/1/2023 warehousing 2324 131131 2324 acme 1 3/1/2023 warehousing 2421 13134 2324 acme 1 4/1/2023 warehousing 425 432121 2324 acme 1 5/1/2023 warehousing 43435 432121 2324 acme 1 6/1/2023 electronics 500 32423 acme 2 1/1/2023 apparel 232 32423 acme 2 2/1/2023 apparel 435 32423 acme 2 3/1/2023 apparel 3453 32423 acme 2 4/1/2023 apparel 231 32423 acme 2 5/1/2023 apparel 65 32423 acme 2 6/1/2023 apparel 321