Forum Discussion
Does HASONEVALUE() not work inside a Calculated Column?
I am trying to use HASONEVALUE() in a Calculated Column to show a column value if a certain field is selected, or otherwise show 0 if that field is not selected.
I tried writing the results as simple 1's and 0's to troubleshoot. Here is the code:
= IF ( HASONEVALUE('table'[column] ), 1, 0)
It just always shows 0 even if I drag the column specified in the HASONEVALUE function into the report. I tried putting this as a measure, and it worked perfectly. The syntax is the exact same between the measure and the column. The only conclusion I could come to is that HASONEVALUE doesn't work in calculated columns for some reason. Does anybody know how to make HASONEVALUE work in a column?
Hi taingw4,
Calculated column is initialized once it has been created regardless of slicer selection. To create a conditional column depend on slicer, you'd better use measure.
If you only need to show result in a card visual, you can create measure like:
HASONEVALUE measure2 = IF(HASONEVALUE(Test_1[Test_1 Cat]),1,0)
If you want to display measure value as a column in a table visual, please try this:
HASONEVALUE measure = CALCULATE(IF(HASONEVALUE(Test_1[Test_1 Cat]),1,0),ALLSELECTED(Test_1))
Best regards,
Yuliana GuHey,
if you are creating a measure you have to be always aware of the following
- a measure returns a scalar value, meaning just one value, by using a certain expression
- the expression is applied to the rows filtered down by slicers, column and row headers
For this reason you have to specify an aggregate function, even if you are certain, that there is just one row, e.g. by testing using HASONEVALUE...
IF the check HASONEVALUE('table1'[Column]) is true, and you know that this means that there is also just one value from 'table2'[column2] selected - from a logical point of view, w/o the necessity that there is some filter propagation happening, you have to, but also can safely use aggregate functions.
If table2 is on the one-side of a relationship you also can use this formula
RELATED('table2'[column2])
Hopefully this helps
Regards
Tom
3 Replies
Hi taingw4,
Calculated column is initialized once it has been created regardless of slicer selection. To create a conditional column depend on slicer, you'd better use measure.
If you only need to show result in a card visual, you can create measure like:
HASONEVALUE measure2 = IF(HASONEVALUE(Test_1[Test_1 Cat]),1,0)
If you want to display measure value as a column in a table visual, please try this:
HASONEVALUE measure = CALCULATE(IF(HASONEVALUE(Test_1[Test_1 Cat]),1,0),ALLSELECTED(Test_1))
Best regards,
Yuliana GuHello Yuliana v-yulgu-msft,
Thank you so much for the explanation. That makes so much sense now why HASONEVALUE() was not working as expectedly inside a column.
A related question: in a measure, is it possible to simply show the column value? I am trying to do something like: IF(HASONEVALUE(Table1'Column1'), Table2'Column2', BLANK()). However it looks like a single value for Table2'Column2' cannot be determined, since I did not use an aggregating function.
Hey,
if you are creating a measure you have to be always aware of the following
- a measure returns a scalar value, meaning just one value, by using a certain expression
- the expression is applied to the rows filtered down by slicers, column and row headers
For this reason you have to specify an aggregate function, even if you are certain, that there is just one row, e.g. by testing using HASONEVALUE...
IF the check HASONEVALUE('table1'[Column]) is true, and you know that this means that there is also just one value from 'table2'[column2] selected - from a logical point of view, w/o the necessity that there is some filter propagation happening, you have to, but also can safely use aggregate functions.
If table2 is on the one-side of a relationship you also can use this formula
RELATED('table2'[column2])
Hopefully this helps
Regards
Tom
