Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
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
Hey,
if you are creating a measure you have to be always aware of the following
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
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
User | Count |
---|---|
91 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
72 | |
60 | |
59 |