Forum Discussion
Cannot apply VALUE on FILTERed table
Hello Daniel,
| Question | Answer | Type |
| Question 1 | 5 | Numerical |
| Question 2 | 3 | Numerical |
| Question 3 | YES | Other |
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
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?
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.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?
Then just a measure of:
Avg of Numerical = AVERAGE(Table1[Custom] )
actually if you put the following as a new column in Power Query, it works as well