Exploiting relationships

Relationships connect tables together within a data model by defining an affiliation between a column in one table and a column in a second table. Creating a relationship between two columns in a table ties the two tables together such that it is expected that values from a column in the first table will be found in the other column in the second table. These table relationships can be exploited by DAX calculations as DAX intrinsically understands these relationships within the data model.

Getting ready

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R07_TableA = GENERATESERIES(DATE(2020,1,1),DATE(2020,12,31))

2. Create a column in this table using the following formula:

Month = [value].[Month]

3. Create a second table called R07_TableB using an Enter Data query with the following data:

Month	Date
January	1/1/2020
February	2/1/2020
March	3/1/2020
April	4/1/2020
May	5/1/2020
June	6/1/2020
July	7/1/2020
August	8/1/2020
September	9/1/2020
October	10/1/2020
November	11/1/2020
December	12/1/2020

4. Ensure that the Date column is formatted as Date/Time.
5. Create a relationship between the Month column in the R07_TableB table and the Month column in the R07_TableA table. Make sure that the cross-filter direction of this relationship is set to Both.
6. Create a measure using the following formula:

R07_CountOfDays = COUNTROWS('R07_TableA')

How to do it...

To demonstrate how relationships work, perform the following steps:

1. On a Report page, create a Table visualization and place the Month column from the R07_TableB table as a field in the visual.
2. While that visualization is still selected, place the R07_CountOfDays measure in the visual.
3. Create a second Table visual and place the Month column from the R07_TableA table as a field in the visual.
4. With this second visualization still selected, drag the Date column from the R07_TableA table into the visual and change its default aggregation to Count.

The first Table visualization lists the months in alphabetical order, while the R07_CountOfDays measure displays the number of days in each month. The second Table visualization also lists the months in alphabetical order and the second column displays 1 for all rows.

How it works...

For the first visualization, placing the Month column from R07_TableB in the visualization creates filter context on the R07_TableA table. Thus, when the R07_CountOfDays measure is calculated in this context, this filter context from the relationship applies to the calculation. Thus, we get the number of rows in R07_TableA that are related to the relevant context. In short, the number of rows in R07_TableA that have the same month as our month values from R07_TableB are displayed.

The second visualization functions in the same way, except that since we placed the Month column from R07_TableA in the visualization and are counting the values in R07_TableB, a 1 is displayed because there is only a single matching row in R07_TableB that matches each distinct Month value in R07_TableA.

There's more...

To explore relationships and how they affect filter context, perform the following steps:

1. Create a second relationship between the Value column in R07_TableA and the Date column in R07_TableB.
2. Ensure that the relationship direction is Both and note that the line is dotted. The dotted line indicates that this relationship is inactive. This is because data models can only have a single active filter pathway between tables.
3. Create the following measure:

R07_CountOfDays2 = CALCULATE([R07_CountOfDays],USERELATIONSHIP(R07_TableA[Value],R07_TableB[Date]))

4. Place this measure in the first Table visualization created.

Note that adding this measure, R07_CountOfDays2, to the first table visualization lists the value of 1 for each month. In addition, an extra row is added to the visualization that has no value for the Month column and lists a value of 354 for the R07_CountOfDays2 measure.

The way that the R07_CountOfDays2 measure works is that we have explicitly overridden the default filter context by the CALCULATE function to explicitly define our filter context and then used the USERELATIONSHIP DAX function to define that filter context. Essentially, we have explicitly told DAX to use the inactive relationship we created as its filter context between the two tables. Thus, only a single row in R07_TableA matches each date value in R07_TableB. However, we have 354 (366 days in 2020 minus 12 matching rows) rows in R07_TableA that do not match any value in R07_TableB, and so this shows up in our table visualization. This actually demonstrates a powerful feature of DAX in helping us find bad data.

See also

For more details regarding this recipe, refer to the following links:

• COUNTROWS: https://docs.microsoft.com/en-us/dax/countrows-function-dax
• CALCULATE: https://docs.microsoft.com/en-us/dax/calculate-function-dax
• USERELATIONSHIP: https://docs.microsoft.com/en-us/dax/userelationship-function-dax