Forum Discussion
Actually I want same custom column I want a write using DAX is it possible ..?? #powerquery
You may feel you are explaining things clearly but you are not. Posting an image and the error message 'DataFormat.Error: We couldn't convert to Number.' doesn't help us to understand what data you are using and what you are trying to do with it.
The error message clearly explains that you are trying to convert something to a number (text?) but that didn't succeed.
Exactly why that has happened will be much easier to work out if you provide the data you are using and show the operation/code/function you are applying to that data to generate that error.
Your initial image shows a column with numbers and text. As has been explained, you can't mix data types in a column. So if you have any text, the whole column is text.
In the latest image, I can see that the column is decimal and the first row is an error so I'm guessing you are trying to convert text (letters) to decimal, which of course won't work.
So you are going to have to convert the entire column to text if you want to keep words Improving and Devaluing in the column along with numbers (which will be stored as text) like -0.09312
Now I think you have asked how can you create the same column using DAX? Well, if you have the data in Power Query, you just convert the column to text then Close & Load to Power BI and the column is available. No need to use DAX to create the column.
If you now want to use the numeric values in the column to do some math operations with, that's a different question and one you will need to explain clearly so we know exactly what it is you are trying to do.
Regards
Phil
8 Replies
Is it possible in Decimal Data type can I add text using custom column?
- PhilipTreacy
Super User
You'll need to explain things a bit more clearly. What is your source data? What exactly is the result you want?
I'm guessing you want a DAX solution to give you a column or mixed data types: text and decimal. But what then?
How is this column created? What's the source data?
Regards
Phil
I mention in Above Comment
Hi SupritDecipher ,
In DAX you cannot mix Decimal and Text in the same column. Power BI does not allow a column to contain different data types. A column must be all numeric or all text, never both.
Please try below alternative options.
1. Create a DAX Calculated Column (Text Only). If you just want the labels (“Improving”, “Devaluing”), then create a text column.
Status =
VAR Value = 'Table'[% Mark]
RETURN
IF(
Value > 0,
"Improving",
"Devaluing"
)2. Keep numeric column separate + add a DAX status column.
% Mark remains Decimal number. Status is created as Text.
3. If you need both number + text together, use a DAX Measure.
In visuals only not a column.
Mark With Status =
VAR val = AVERAGE('Table'[% Mark])
RETURN
IF(
val > 0,
FORMAT(val, "0.00") & " (Improving)",
FORMAT(val, "0.00") & " (Devaluing)"
)If my response as resolved your issue please mark it as solution and give kudos.
Thanks,
Dinesh
Hi SupritDecipher
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank youNot Resolved Yet
DataFormat.Error: We couldn't convert to Number.
Details:
NA
How can resolved in DAX Custom Column..?- PhilipTreacy
You may feel you are explaining things clearly but you are not. Posting an image and the error message 'DataFormat.Error: We couldn't convert to Number.' doesn't help us to understand what data you are using and what you are trying to do with it.
The error message clearly explains that you are trying to convert something to a number (text?) but that didn't succeed.
Exactly why that has happened will be much easier to work out if you provide the data you are using and show the operation/code/function you are applying to that data to generate that error.
Your initial image shows a column with numbers and text. As has been explained, you can't mix data types in a column. So if you have any text, the whole column is text.
In the latest image, I can see that the column is decimal and the first row is an error so I'm guessing you are trying to convert text (letters) to decimal, which of course won't work.
So you are going to have to convert the entire column to text if you want to keep words Improving and Devaluing in the column along with numbers (which will be stored as text) like -0.09312
Now I think you have asked how can you create the same column using DAX? Well, if you have the data in Power Query, you just convert the column to text then Close & Load to Power BI and the column is available. No need to use DAX to create the column.
If you now want to use the numeric values in the column to do some math operations with, that's a different question and one you will need to explain clearly so we know exactly what it is you are trying to do.
Regards
Phil
