cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cannot apply VALUE on FILTERed table

Hello all,

 

Context

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

 

Approach

To do so, I tried to:

  1. Filter the table
  2. Apply Value on the filtered table so I can only convert the numerical values to integer

Wich resulted in the following expresson:

 

 
measure = AVERAGEX ( FILTER(ALL(data) ; data[Type_Question] = "Numerical_Question" )      ;     VALUE([Answer])   ) 
 
I get the following error when applying this formula:
dax cannot convert value '' of type text to type integer
 
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 ALL  the rows of the data table.
 
How can I prevent the function from doing so?
 
I want to FILTER then convert with VALUE on the filtered rows.
 
Regards,
 
 
9 REPLIES 9
Anonymous
Not applicable

Re: Cannot apply VALUE on FILTERed table

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

Microsoft
Microsoft

Re: Cannot apply VALUE on FILTERed table

Hi @Anonymous,

Based on test, it may caused by the null value in your data, you could refer to below steps:

Sample data:

1.PNG

Create below measure:

measure = AVERAGEX ( FILTER(ALL(data),data[Type_Question] = "Numerical_Question"&&'data'[Answer]<>BLANK()), VALUE([Answer]))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Cannot apply VALUE on FILTERed table

Hello Daniel,

 

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.

 

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?

 

something like :

VALUE(data_filtered[Answer])

 

You see my point?

Microsoft
Microsoft

Re: Cannot apply VALUE on FILTERed table

Hi @Anonymous,

Could you please share some sample data to have a test?

 

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Cannot apply VALUE on FILTERed table

Hello Daniel,

 

 

QuestionAnswerType
Question 15Numerical
Question 23Numerical
Question 3YESOther


  

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.

 

I found a turnaround for this problem and I changed the query as suggested, in order to filter the 'Answer' column 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.

 

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.

 

If you have any other solutions I'm willing to hear it.

 

Have a nice day

 

 

Anonymous
Not applicable

Re: Cannot apply VALUE on FILTERed table

You cannot mix data types in the same column like you do with your Answer.  So it will default to text in this case, and you cannot average text.  Easiest thing to do is in Power Query, Reference the Main query, but filter down to just Numerical and then a simple average function over that answer column after you change the data type to a numerical one.   I guess you could create a new column in the same table to only give a number, but why make it more complicated then it needs be?

Anonymous
Not applicable

Re: Cannot apply VALUE on FILTERed table

Hello Nick,

 

I understand that you can't mix data type.
But one should be able to convert the rows of a filtered table only right?

 

I don't want to change the query because, I will need to realize different measures from the non-numerical answers in the future.

And then I would have to change the query again to filter what I want.. it's unpractical.

 

I could create a column and convert non-numerical answer to numerical answer by changing the scale, but I want to avoid touching the data as much as possible here.

I want to encapsulate the whole complexity of this operation (which is not very complex actually.. ) in the DAX measure.

 

Anonymous
Not applicable

Re: Cannot apply VALUE on FILTERed table

I dont think you can change the data type via a measure.  But what if you add the following as a custom column to your query?

 

Number otherwise null.png

Then just a measure of:

Avg of Numerical = AVERAGE(Table1[Custom] )

 

Anonymous
Not applicable

Re: Cannot apply VALUE on FILTERed table

actually if you put the following as a new column in Power Query, it works as well

Number otherwise null.png

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors