Relationship between multiple tables

[amani1980]

Hi,

I have three tables (named Event, Ticker, Trial) with a country, event, operation, date etc. I want to link them without doing a major merge which would be very cumbersome. The values called Ticket repeats among the three queries (event, Ticker & Trial). I have created a new query for Country (no duplicates) and linked it to the other three queries. For date, I created a new date query with (CALENDAR(DATE(2023,1,1), DATE(2024,12,31))) this formula so as to use this as a common fliter/slicer for the three tables. However, I'm having trouble getting it to work. The date slicers seem random and the relationships are messy too. I want to see a sumary table of Country wise count of Events, Tickets and Trials by date. I'm uploading the pbix on Drive.

Not sure if this is the best way to get this dataset to work. Any help would be appreciated.

Thanks in advance!

-AM

[Anonymous]

Hi @amani1980 ,

Thanks for @lbendlin 's solution , If it helped you, please accept it as a solution. This will be of great help to other users who have similar problems as you.

The intricate relationships within your data can easily lead to various issues. Given that several of your tables are dimension tables, I would recommend using a star schema in your data modeling approach to simplify and enhance clarity.

I think this link will hlep you a lot:

star schema

Best Regards,

Bof

[lbendlin · Accepted solution]

Ticket_Table is a dimension table, not a fact table

the relationship to Ticker is the wrong way round.

Next, you made assumptions about VALUES  that turn out not to be true

Let's fix that

Now we're talking. At this point I don't know what the business relationship is between Ticker and Trial - you need to explain that.

[amani1980]

Hi,

Thanks for your reply! Understandably I'm a novice to Power BI. So please bear with me...

The business relation between Event, Ticker and Trial is this - Let's ignore the table "Ticker" for now. Trial table contains all the trials that were conducted in a country with the associated Ticket. You could have multiple Trials inside each ticket - (same person receiving multiple trials but associated with one bill). If an issue arises during the Trial, then an entry is made inside the Event table. Event table contains all the events that happened in a specific country on a specific date. Basically, whatever accident/issue happened during a trial is captured in Event table under a unique event number. Each event has an related Ticket to it. So the ticket inside "Event" is a subset of the Tickets inside the table called "Trial".

What I want to do is do an analysis of how many events happened vs number of Trials vs No of Tickets vs which country vs Date.

For instance, how many issues happened in Andorra in 2024 compared to the number of Tickets or the trial efficiency (viz., no fo events/No fo Trials).

I'm looking into the file you sent!
Thanks!