Complex modeling with 4 tables. need help!

[Sut_Datanaut]

Ok, so i have a rather large request that I have been mulling over for weeks. I have 4 central tables, along with other key tables (no need to get into these, but wanted to note it)

These 4 tables each have an account ID number, a date, and their own unique fields

1. one table has account volume and revenue

2. one table has account support desk calls

3. one table has survey responses 

4. one table has no date, but is used as a key for segmentation purposes

I would like to combine all of these tables, or atleast use them as outer links to a central table that i can then combine. the problem I am running into is that each table will have multiple rows for account number either for the date, for the application they use or origin of the support desk calls, so i end up getting a ton of singlular value expected errors.

Right now I am using the survey responses table as the main one that has many to many connections with the other tables (each table will have a different set of account ID numbers, but I am only looking for account ID's that are found on all 4 tables.

Any help would be appreciated

[lbendlin]

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?

[Sut_Datanaut]

Also, all 4 are fact tables, but i would like the category fields from each table to use across all tables. 

[lbendlin]

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.

[Sut_Datanaut]

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

[Sut_Datanaut]

Yeah, so i am lookingto build a star schema, the difficulty is that the dims tables have multiple distinct ID rows due to multiple categories they are in. So ACME, under ID number 2342 is present in 2 rows because they are in industry apparel and electronics.

So it would look like this, where I am obviously getting singluar value expected errors. There are associated revenues per each one of these categories, so do I have to do top 1 of each account id so there is only 1 row for each unique ID? 

Thanks,

Account ID	Account Name	Industry
2324	acme	apparel
2324	acme	electronics
131131	fred	warehousing
13134	sarah	apparel
432121	jessica	warehousing
432121	jessica	electronics