In the previous lesson, we were able to use DAX to ascertain the Number of Staff born on a particular year which was then represented on a Matrix Visual where we expressed the Year and each staff that was born on that year.
In today’s lesson, considering the same scenario which we used in the previous lesson, we are to ascertain the Number of Staff joined in a particular year and then express them on a Matrix Visual as well.
Still, on the two tables used in the previous lesson, the Date table contains the following field.
- Date Key
- YearNumber
- MonthNumber
- MonthName
- DayNumber
- DayName
- Quarter
While the Staff Table contains the following Field
- Staff Number
- First Name
- Last Name
- Date Born
The Date Table is related to the Staff Table (through the Date Born and Date Joined Columns) with the Date key Column on the Date Table.
Note that For the Date Born column on the Staff Table and the Date Key Column on the Date Table is an Active relationship but for the Date joined column on the Date Table (which we need to calculate the Number of Staff Joined for each Year) and the Date Key Column on the Date Table is an inactive relationship. How do we deal with the Inactive relationship between Tables on a Power BI Model?
For the inactive relationship, we will make use of the USERELATIONSHIP Function in DAX to use the Date Key Column (Date Table) to filter the Date joined Column (Staff Table).
The COUNTROW Function Counts Objects iterating over the Staff Table; COUNTROWS(Table)
The CALCULATETABLE Function which Evaluates the Staff Table Modified by a Filtered Expression.
CALCULATETABLE(Table, Filter Expression…)
Number of Staff Joined by Date = COUNTROWS(CALCULATETABLE(Staff,USERELATIONSHIP(Dates[DateKey],Staff[DateJoined])))
At the Report Section Click on the Table Visualization tool then move the Year Number Column from the DATE Table to the Values Field and the New Measure you just calculated to the Values Field section also as shown.
Hope you found this helpful?
Do not forget to check out our training courses: https://www.urbizedge.com.
