DAX – Reverse Polarity RELATED()

Guest post by Ken Puls

I was recently working on an interesting thing in PowerPivot, and thought I’d share it in case it may help someone… A quick summary of the issue is that I needed to examine multiple records in a child table, and return a single result to the parent table if a condition was present; something that goes against the normal relationship flow.

Scenario Background

I have a file that tracks the purchase and sale of land, as well as the respective property taxes and assessments that we get on an annual basis. It is set up like this:

At first glance this might look complicated… Each Parcel is assigned a unique Parcel Identifier, known as a PID, a key identifier that will never change for a piece of property.  This info is then used to identify that parcel by the tax authority, the assessment authority, and our appraisers and accounting systems.  In the case above, we can see that the PID from the Parcel table links to the Transaction, Taxes and Assessments tables. From the other side we’ve got the Key_Date that links back to the three tables as well, although it links to TranDate, TaxYear and AssessYear respectively.

This setup works well, as I can use items from either the Parcels or Calendar tables in rows or columns on pivot tables or in slicers to control the filter context for the value area. In short, I know that any value from the Transactions, Taxes or Assessments tables will have consistent filter contexts and I’ll never see an error about relationships that can’t be defined.

From this setup I created a simple Pivot with the PID from the Parcels table on rows, and four measures from the Assessments table. I also linked the Key_Date from the Calendar table to a timeline:

Pretty good, except that I’m trying to compare the assessments from year to year. There is a hole in the current year value for one of the PID’s and, after a bit of looking, I discovered that the property was sold. And here I met my issue; it doesn’t make sense to include properties that are sold, as they mean I can’t make an apples to apples comparison of the assessments between years. I need to filter them out. And now things get tricky…

The Problem

The problem is that the information I need to tell if a lot is sold is in the Transactions table, (it is held in the Transaction Type field,) and that table has a relationship that flows the wrong way against the Parcels table. I always look at relationships like one way streets – both the Transactions table and the Assessments tables have arrows that flow into the Parcels table, which means I can’t filter the Transactions table and have it influence the Assessments table.

In order to be able to accomplish my goal, though, I really need be able to add a slicer to my project that allows me to filter out any Parcel that has not been sold. That field needs to exist in the Parcels table so that I can use it on the Taxes table at a later date as well.

So here’s what I have:

• The PID field in the Parcels table holds unique parcel identifiers.
• There can be multiple PIDs in the Transactions table
• One of the states of “Transaction Type” in the Transactions table can be “Sold”

So what I needed to do was find a way, from the Parcels table, to show if a record existed in the Transactions table which marked that PID as sold. (Once property is sold it can’t be unsold, so that’s a good thing.) But how to do it?

The Solution

As an Excel pro, I really wanted to do a COUNTIFS to check if the PID matched and if the Transaction Type was Sold. Unfortunately DAX doesn’t have a COUNTIFS function, so now what?

Even after working with PowerPivot for as long as I have now, I seem to have to keep reminding myself that PowerPivot works by the following method:

• Filter your records
• Perform math on what’s left

Even today that still feels somewhat backwards to me, as I feel Excel works from the reverse angle, but whatever. Thinking this way gets you to where you can solve the problem.

Step 1: Filter your records… I can use FILTER to create a filtered table to work with. So I visualize the table I need:

FILTER(Transactions,Transactions[Transaction Type]=”Sold”&&Transactions[PID]=Parcels[PID])

So basically, I’m going to create a virtual table by filtering the Transactions table to include records where the Transaction Type = “Sold” and (represented by the &&) where the Transactions PID = the Parcels PID.

Now, this would not work purely as a measure, as it would choke on the context of the Parcels[PID], not knowing which row of the Parcels table to use. But using it in a calculated column in the Parcels table is a different matter. At that point the row that the formula is in will pare down that context so it knows the PID is the PID from that specific row of the Parcels table. Cool!

Of course, that just gives me my table to work with…

Step 2: Now I need to perform a calculation on the filtered records. This is actually the easy part: wrap it in COUNTROWS()

=COUNTROWS(FILTER(Transactions,Transactions[Transaction Type]=”Sold”&&Transactions[PID]=Parcels[PID]))

This works perfectly, like a great big COUNTIF statement. Each row of the Parcels table executes the formula. Working from the outside-in, it does the following:

• It filters the Transaction table down, removing any records where the Transaction Type is not “Sold”
• It then filters the Transactions table down further, removing any records where the PID doesn’t match the PID from that row of the Parcels table
• Next it counts the rows in the filtered table, if any

The values I get back are either blank (no records for that PID are marked Sold), or a value of 1. Beautiful! The final step is just to wrap it in an IF statement:

=IF(COUNTROWS(FILTER(Transactions,Transactions[Transaction Type]=”Sold”&&Transactions[PID]=Parcels[PID]))>0,”SOLD”,”Held”)

For the record, I called this calculated field “Status”. As it now rests in the Parcels table, I can add it to the rows of my Pivot, as well as use it in a slicer, as shown:

Bye, bye sold lot!

Closing Thoughts

What makes this interesting to me is that it goes against the normal relationship flow where the parent table filters out records in the child table.

It’s worth saying that I probably could have just adjusted my measure to do this as well, but to be honest, this method give me more flexibility. I can very quickly toggle the Held/Sold switch to see my total property assessments on the year, and I can also use this same setup to work with the Taxes table in future.