DAXDAX Trainer

DAX Concept

Calculating Related Row Counts with COUNTROWS and RELATEDTABLE

Learn to use COUNTROWS in conjunction with RELATEDTABLE to count rows in related tables and then perform calculations based on these counts, creating new calculated columns for insightful data analysis.

IntermediateCOUNTROWSRELATEDTABLE

Concept Breakdown

COUNTROWS is a DAX function that counts the number of rows in a specified table. When used in a row context (like within a calculated column), it counts rows based on the current row's context. RELATEDTABLE is a DAX function that retrieves a table related to the current row, filtered by the current row's context. Combining COUNTROWS(RELATEDTABLE(<table>)) allows you to efficiently count all related rows from a 'many' side table for each row in the 'one' side of a relationship, directly within a calculated column.

What You Will Learn

  1. Understand when to use COUNTROWS, RELATEDTABLE in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a intermediate-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided Power BI file to access the dataset.

  2. Open the Power BI file and navigate to the 'quadrant' table.

  3. Create a new calculated column in the 'quadrant' table and name it 'Number of regions'.

  4. Write a DAX formula for 'Number of regions' using COUNTROWS and RELATEDTABLE to count the number of regions associated with each quadrant.

  5. Create a second calculated column in the 'quadrant' table and name it 'Number of towns'.

  6. Write a DAX formula for 'Number of towns' using COUNTROWS and RELATEDTABLE to count the number of towns associated with each quadrant.

  7. Create a third calculated column in the 'quadrant' table to calculate the ratio of towns to regions. Name this column 'Towns per Region Ratio' and use the previously created columns.

  8. Build a table visual using the 'quadrant' table, including the quadrant name and the three new calculated columns. Order this table by 'Towns per Region Ratio' to see town density.

  9. Save the Power BI file with the name 'Go East young woman'.

Starter DAX

New Column = COUNTROWS(RELATEDTABLE(

Expected Outcome

A table visual displaying quadrants with their respective 'Number of regions', 'Number of towns', and 'Towns per Region Ratio'. The table should be sorted by 'Towns per Region Ratio', showing which quadrants have the highest 'town density' (e.g., the South having the most towns).

Checking your sign-in status...