Forum Discussion

powergreen24's avatar
powergreen24
New Member
1 year ago
Solved

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...
  • johnt75's avatar
    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'.

     

  • MFelix's avatar
    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: