Forum Discussion
Creating a Decision Matrix
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!
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'.
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:
2 Replies
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:
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'.