Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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)
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)
Works perfect, thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |