Forum Discussion
Ambiguity and cardinality
Good afternoon
I am relatively new to power bi, I have always managed in the data modeling part well because I always work with few tables (a data table and some lookup tables, always 1 to many). However I am facing a problem because I have two data tables and two or 3 lookup tables that I am not able to aggregate. I will try to describe:
- Table CYCLES - main DATA TABLE : Each row is a cycle/service/ of a primary ID and siteId. Each ID can have several cycles and in each cycle several services. There are some Site Id - SiteId are diferent deposits where these primary ID are with their Cycles and services.
- Table CENSUSES- Each day per SiteID is a row, it gives the census of all primary ID for SiteId. It should be a lookup table but it has equal dates for different siteId so it becomes data table, no? (I already tried to concatenate "Date&SiteId" and thus make lookup table but I did not get the result i want).
- CALENDAR table - a normal calendar lookup table attached to matingdate from Cycles table)
- Table DadosGranja - lookup table with SiteID and general data of the same attached to SiteId from Cycles table (by both ways - I get better results than 1 way). This is where I wanted to connect CYCLES table with CENSUSES table by site Id. This table has no dates columns
- Table Sows - Lookup table without problem because it is linked to Cycles table as it has little information so I maded 3 or 4 related DAX formulas...
I'm already a bit confused...
The question now is to link , CENSUSES, CYCLES and DataGranja. I have the work almost done but I do not know how to connect the CENSUSES table. If to CALENDAR by date (*-*), if to CYCLES by "Date&SiteId" (1-*), if to DadosGranja by site ID (1-*)?
Remenber that 90% of information its in CYCLES table.
Some examples... wich one its better? or there is a better one?
Thanks!
"fact" tables contain numbers that you do math on. "dimension" tables are used for grouping and filtering. "dimension" tables control "fact" tables. Don't link "fact" tables directly to each other.
1 Reply
- lbendlin
Super User
"fact" tables contain numbers that you do math on. "dimension" tables are used for grouping and filtering. "dimension" tables control "fact" tables. Don't link "fact" tables directly to each other.