Count Values from one table using based on month and year and add to another table (Pbix included)

---

HotChilli wrote:

Power Query:

Add a column for 1st day of the month (can do this from the interface->Select date column, go to Add Column-> From Date and Time on the ribbon)

Remove the original date column.

Group By StartOfMonth and Incident using Count Rows as aggregation.

This gives a 3 column table which is pretty good for storage and analysis.

If you want a table as shown, then Pivot the IncidentType column

---

Thanks,

I didnt check my emails all day and so i didnt see your response and only came back here to post my answer, ill give you the solutions, because your right that would have been correct. But heres what i did to get the same results

I created a simple distinct measure in DAX

TotalInjuries = DISTINCTCOUNT(InjuriesTable[Injury Type])

and then created a table referencing the measure and using several if statments to group it by, it then created a table like i wanted and referenced the date as i set up a one to many relationship.

IncidentTotalTable = ADDCOLUMNS(IncidentTable, "Date", DATEVALUE(IncidentTable[InjuryDate]), "FAI" , IF( [Injury Type] = "FAI", [TotalInjuries] ), "MTI" , IF([Injury Type] = "MTI",  [TotalInjuries]), "LTI" , IF([Injury Type] = "LTI", [TotalInjuries]))

 I like your way better, except i would have had to create another table.