Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ruhor
Frequent Visitor

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 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."

1 ACCEPTED SOLUTION
greggyb
Resident Rockstar
Resident Rockstar

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

View solution in original post

2 REPLIES 2
ruhor
Frequent Visitor

Thanks!

greggyb
Resident Rockstar
Resident Rockstar

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.