Forum Discussion

DBrito79's avatar
DBrito79
Frequent Visitor
3 years ago
Solved

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:

  1. 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.
  2. 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).
  3. CALENDAR table - a normal calendar lookup table attached to matingdate from Cycles table)
  4. 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 
  5. 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

  • "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.