Forum Discussion
Does HASONEVALUE() not work inside a Calculated 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
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 Gu
Hello 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