Filling in Calculated Colmn Values if an Entry Exists in Another Table

[ruhor]

I have two DB tables which I imported into Power BI. One is a table of rental properties named "property" and the other is a table of metered utilities named utilityprops. I added a new column to the property table and named it "metered". I would like to write a DAX statement that matches propid from the Property table with a utilid id in the utilityprops table, if it finds an entry for a metered utility in utilityprops table it would return a 1 in the column for [Metered], if it can't find an entry for the utilid it would put a 0 in the [Metered] column. Properties that have metered utilities will almost always have more than one utilid entry in utilityprops for a given propid, since most of the time, when water is metered so is sewer. This is what the property table looks like:

propid	name		shortname	street	city	state	zip......	metered (new column)
1	oakwood
5	pleasantville
8	riverside
10	needle rock
22	foggy ridge
23	green park
24	cripple creek
25	royal oaks
37	riverbend
55	bates hotel

And this is what utilityprops table looks like:

utilid		propid
218		1	
219		1	
222		5
223		5
225		8
226		8
228		22
229		22
230		23
231		23
232		24
233		24
236		25
237		25
245		55

This is what I am aiming for after the calculated column is populated in the property table:

propid	name		shortname	street	city	state	zip......	metered
1	oakwood									1
5	pleasantville								1
8	riverside								1
10	needle rock								0
22	foggy ridge								1
23	green park								1
24	cripple creek								1
25	royal oaks								1
37	riverbend								0
55	bates hotel								1

I setup a relationship between the property table and the utilityprops table using the propid. Would it be possible to do a simple If statement like:

metered = IF (
    ISBLANK('ruhor_views utilityprops'[utilid]),
    0,
    1
)

Thanks!

[Greg_Deckler · Accepted solution]

Make sure that utilityprops is related to properties (many to one) and then use this for your metered column:

metered = IF(COUNTX(RELATEDTABLE(utilityprops),'utilityprops'[propid])>0,1,0)

[ruhor]

Works perfect, thanks.