Extracting Text Value in DAX

[ruhor]

Im trying to extract a YES/NO text data feild for a rent managment dashboard, the value indicates whether water charges are included in rent, so each property will only have one value (which happends to be a userdefined feild with id of 56).

My data table looks like this->

userdefinedid	propid	value		userid	updated		datecreated
---------------------------------------------------------------------------
53		91			40	2012-09-27	2012-09-27
54		91	District-6	40	2014-06-12	2014-06-12
55		91	NO		40	2013-05-07	2013-05-07
56		91	NO		40	2013-05-07	2013-05-07

Filtering on userdefinedid and propid makes this value unique.

My DAX code looks like:

isWaterIncludedInRent = IF(HASONEVALUE(propuserdefinedvalues'[value]),CALCULATE('ruhor_views propuserdefinedvalues'[value], 
                          FILTER('ruhor_views propuserdefinedvalues', 'ruhor_views propuserdefinedvalues'[userdefinedid] = 56)))

There is a slicer in the dashboard which filters the propid, currently I get the following error message 

"A single value for column 'userdefinedid' in table 'ruhor_views propuserdefinedvalues' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[ruhor]

Thanks!

[greggyb · Accepted solution]

First, DAX Formatter is your friend.

Second, column references only evaluate to scalars in row context. Measures operate in filter context. You need to wrap every column reference outside of row context in some function.

InRent = IF(
    HASONEVALUE( propuserdefinedvalues'[value] )
    ,CALCULATE(
        VALUES( 'ruhor_views propuserdefinedvalues'[value] )
        ,'ruhor_views propuserdefinedvalues'[userdefinedid] = 56
    )
)

This should do it for you. VALUES() returns the distinct values making up a field (in filter context), and its result can be coerced to a scalar value if there is only one distinct value.

Secondly, for simple predicates, there's no need to use FILTER(), CALCULATE() can take simple literal predicates as direct arguments.

Lastly, the measure I've provided shouldn't throw any syntax errors at you, but you're checking for one value of [value] BEFORE you're applying a filter on [userdefinedid]. Thus, this measure will be blank if the [propid] selected has multiple distinct values of [value] across all [userdefinedid]s.

You'll probably need to change it to something like the following:

InRent = IF(
    CALCULATE(
        DISTINCTCOUNT( 'ruhor_views propuserdefinedvalues'[value] )
        ,'ruhor_views propuserdefinedvalues'[userdefinedid] = 56
    ) <= 1
    ,CALCULATE(
        VALUES( 'ruhor_views propuserdefinedvalues'[value] )
        ,'ruhor_views propuserdefinedvalues'[userdefinedid] = 56
    )
)