Forum Discussion

ruhor's avatar
ruhor
Frequent Visitor
10 years ago
Solved

Extracting Text Value in DAX

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...
  • greggyb's avatar
    10 years ago

    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
        )
    )