Forum Discussion
powergreen24
1 year agoNew Member
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.). T...
- 1 year ago
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'.
- 1 year ago
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
1 year agoSuper User
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'.