Creating a summary from 2 tables

[stgiesbrecht]

I have two data sources that look as follows:

Table1 - ID, Date, Description of event, ...

Table2 - ID, Date, Near Miss (true/false), Injury (choice of None, Slight Injury, First Aid, Medical Aid), Description of event, ...

and would like to get a summary table that looks like so

ID, Date, Type of event, Description of event

for example

10, 2017/09/20, Near Miss, description 1 (from Table2)

11, 2017/10/04, Slight Injury, description 2 (from Table2)

11, 2017/09/16, Incident, description 3 (from Table1)

12, 2017/10/15, First Aid, description 4 (from Table2)

13, 2017/11/05, Near Miss, description 5 (from Table2)

14, 2017/10/15, Incident, description 6 (from Table1)

12, 2017/10/02, Incident, description 7 (from Table1)

etc.

This is so I can then summarize and graph as follows

Incident - 3 occurences

Near Miss - 2 occurences

Slight Injury - 1 occurence

First Aid - 1 occurence

and would like to use the funnel graph, so that the order of the event type is in the following order

Incident

Near Miss

Slight Injury

First Aid

Medical Aid

Any assistance would be greatly appreciated,

Scott

[stgiesbrecht]

One thing to add is that the a record in Table2 might have Near Miss with a value of true and Injury with a value of Slight Injury which I would then want to have 2 records in the summary table

Table2 - 25, 2017/12/03, Near Miss = true, Injury = Slight Injury

so new summary table would be

25, 2017/12/03, Near Miss, description 1

25, 2017/12/03, Slight Injury, description 1

[Greg_Deckler]

Depends on whether you want to do this in M or DAX. But, I would concatenate your ID and Date fields together into a new column and then you can use a Merge query or a NATURALINNERJOIN function to merge them together.