Cannot apply VALUE on FILTERed table

Question

Hello all,&nbsp;ContextI have a table with a column named "Answer"I want to measure the average of all numerical answer.Because, see, some answer are numerical and some answer are textual, but because of that, they're all considered as "text" by default by Power BI.&nbsp;ApproachTo do so, I tried to:Filter the tableApply Value on the filtered table so I can only convert the numerical values to integerWich resulted in the following expresson:&nbsp;&nbsp;measure&nbsp;= AVERAGEX ( FILTER(ALL(data) ; data[Type_Question] = "Numerical_Question" )&nbsp; &nbsp; &nbsp; ;&nbsp; &nbsp; &nbsp;VALUE([Answer])&nbsp; &nbsp;)&nbsp;&nbsp;I get the following error when applying this formula:dax cannot convert value '' of type text to type integer&nbsp;It seems like the Value function doesn't apply only on the rows of my filtered table which contains only numerical values (formatted as text) but on all the , but on&nbsp;ALL&nbsp; the rows of the data table.&nbsp;How can I prevent the function from doing so?&nbsp;I want to FILTER then convert with VALUE on the filtered rows.&nbsp;Regards,&nbsp;&nbsp;

anonymous · Answer

can you post some sample data?&nbsp; Sounds like it would be much easier to these transformations in Power Query and then write some simple dax...

v-danhe-msft · Answer

Hi&nbsp;Anonymous,
Based on test, it may caused by the null value in your data, you could refer to below steps:
Sample data:

Create below measure:
measure = AVERAGEX ( FILTER(ALL(data),data[Type_Question] = "Numerical_Question"&amp;&amp;'data'[Answer]&lt;&gt;BLANK()), VALUE([Answer]))
Result:

You could also download the pbix file to have a view.
&nbsp;
Regards,
Daniel He

anonymous · Answer

Hello Daniel,&nbsp;thank you for your answer, but it is still not working.You are right, I suspected some null values here, because there are.. but I know for sure that the condition in my filter is sufficient to filter out all non numerical value.Nevertheless, I tried your condition and I still get the same error.&nbsp;To me, the problem is that the VALUE() function doesn't apply only on the filtered values (no matter the condition stated), but to all rows of my 'data' table..How can I refer to my filtered table instead?&nbsp;something like :VALUE(data_filtered[Answer])&nbsp;You see my point?

v-danhe-msft · Answer

Hi&nbsp;Anonymous,
Could you please share some sample data to have a test?
&nbsp;
&nbsp;
Regards,
Daniel He

anonymous · Answer

Hello Daniel,&nbsp;&nbsp;QuestionAnswerTypeQuestion 15NumericalQuestion 23NumericalQuestion 3YESOther&nbsp;&nbsp;Here you have a sample.My goal is to have the average of all the numerical values.Please note there are no empty Answer value in the filtered table, I checked it.You do have empty rows in the full table though, that's the key element here.&nbsp;I found a turnaround for this problem and I changed the query as suggested, in order to filter the 'Answer' column&nbsp;and format it prior to doing any DAX.It work fine.. but I don't want to do this. I want to be able to filter it with DAX.&nbsp;Given what I said earlier, it seems that the VALUE() function doesn't apply on my filtered table.. but on the full table, which have empty values.&nbsp;If you have any other solutions I'm&nbsp;willing to hear it.&nbsp;Have a nice day&nbsp;&nbsp;

Forum Discussion

Cannot apply VALUE on FILTERed table

9 Replies

Related content

Apply data to all rows in a filtered column

Apply Filter before Calculating Average

Logging Applied Filters in Power BI Report Server

Can’t apply filter to measure in visual filter pane (grayed out / locked)

Filter does not apply properly