Creating a Decision Matrix

[powergreen24]

I have two excel files that I'm trying to use to create a decision matrix. The first would contain information about a clients criteria for buying a home (Min # of Bedrooms, Bathrooms, Sqft, etc.). The second file would contain a list of properties in the area and their specifications (The house on 2389 Wayne Street has 3 Baths, 5 Bedrooms, and is 3,000 sqft, etc.).

The powerbi visual would have a slicer with each clients name so that when I click "Client 1", the table with the properties information would be filtered to only show those that meet the clients minimum criteria. I've tried to accomplish this a few different way without success (relationships in the data model, filtering, etc.), would appreciate any suggestions! 

Property Decision Matrix.pbix

[johnt75 · Accepted solution]

You could create a bridge table using

Matching Properties = SELECTCOLUMNS(
	GENERATE(
		'Client Data',
		FILTER(
			'Properties Data',
			'Properties Data'[Attached Garage] = 'Client Data'[Attached Garage]
				&& 'Properties Data'[Baths] >= 'Client Data'[Min Baths]
				&& 'Properties Data'[Media Room] = 'Client Data'[Media Room]
				&& 'Properties Data'[Office] = 'Client Data'[Office]
				&& 'Properties Data'[Pool] = 'Client Data'[Pool]
				&& 'Properties Data'[Rooms] >= 'Client Data'[Min Rooms]
				&& 'Properties Data'[Sqft] >= 'Client Data'[Min Sqft]
		)
	),
	"Client Name", 'Client Data'[Name],
	"Property ID", 'Properties Data'[ID]
)

Then create a one-to-many relationship from 'Client Data' to 'Matching Properties' and a many-to-many single direction relationship from 'Matching Properties' to 'Property Data' so that 'Matching Properties' filters 'Property Data'.

[MFelix · Accepted solution]

Hi @powergreen24 ,

For this you need to create a measure similar to this:

	Filter =
	IF(
		ISFILTERED('Client Data'[Name]),
		COUNTROWS(FILTER(
			'Properties Data',
			'Properties Data'[Baths] >= MIN('Client Data'[Min Baths]) &&
			'Properties Data'[Rooms] >= MIN('Client Data'[Min Rooms]) &&
			'Properties Data'[Sqft] >= MIN('Client Data'[Min Sqft]) &&
			'Properties Data'[Office] = MIN('Properties Data'[Office]) &&
			'Properties Data'[Attached Garage] = MIN('Client Data'[Attached Garage]) &&
			'Properties Data'[Media Room] = MIN('Client Data'[Media Room]) &&
			'Properties Data'[Pool] = MIN('Client Data'[Pool])
		))
	)

Be aware the I'm assuming that when the Yes / No fields must be equal:

Now use it on your matrix has  a filter different from blank:

[johnt75 · Accepted solution]

You could create a bridge table using

Matching Properties = SELECTCOLUMNS(
	GENERATE(
		'Client Data',
		FILTER(
			'Properties Data',
			'Properties Data'[Attached Garage] = 'Client Data'[Attached Garage]
				&& 'Properties Data'[Baths] >= 'Client Data'[Min Baths]
				&& 'Properties Data'[Media Room] = 'Client Data'[Media Room]
				&& 'Properties Data'[Office] = 'Client Data'[Office]
				&& 'Properties Data'[Pool] = 'Client Data'[Pool]
				&& 'Properties Data'[Rooms] >= 'Client Data'[Min Rooms]
				&& 'Properties Data'[Sqft] >= 'Client Data'[Min Sqft]
		)
	),
	"Client Name", 'Client Data'[Name],
	"Property ID", 'Properties Data'[ID]
)

Then create a one-to-many relationship from 'Client Data' to 'Matching Properties' and a many-to-many single direction relationship from 'Matching Properties' to 'Property Data' so that 'Matching Properties' filters 'Property Data'.