The Relationship Between DAX and Visualization in Power BI (Part 2)

The Relationship Between DAX and Visualization

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])))

The Relationship Between DAX and Visualization

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.

The Relationship Between DAX and Visualization
The Relationship Between DAX and Visualization

Hope you found this helpful?

Do not forget to check out our training courses: https://www.urbizedge.com.

Leave a Reply

Business Data Analysis
close slider
[]
1 Step 1

Interested in our Business Data Analysis Training?

keyboard_arrow_leftPrevious
Nextkeyboard_arrow_right