DAX measure to sum only numeric values on string column with both numbers and strings

0
votes

How to create DAX measure to sum up only numeric values? Suppose we have simple sample data as below. enter image description here

I thought that this code might do the job, but it failed. 

m1 = IF( MAX(Table1[category]) = "apples", SUM(Table1[units]) , BLANK() )

When I try to add this measure to the table, which has applied filters for apples and plums only, I get the error message:

enter image description here

I need something that will apply filter first, seeding out text values, then do the summing up on numeric values only.

2
powerbidax
By design, a database can only store one datatype in a column. The only way you could include those mix of values is to store them as strings. While it can be done- making sense of the data afterwards is problematic since the intent is to select certain strings over other strings, converting the applicable strings to integers. This requires running a function over the entire data- 'unpacking' in a sense, which is a violation of 1NF in database design. – Zorkolot
Nevertheless, for the sake of flexibility, it is convenient to have mixed values and sum only numeric values. – Przemyslaw Remin

2 Answers

2
votes

This is a terrible way to store data, but it is possible to do what you're asking.

Try this measure, for example,

= SUMX(FILTER(Table1, Table1[type]="number"), VALUE(Table1[units]))

The VALUE function converts the string to a numeric value and we only sum over the rows where the type is "number".

0
votes

The main problem is that SUM will never work on a column that is defined as being text. So for example if your column was only numbers, but the column defined as text then Measure:= SUM ( table[ units] ) would always error.

The only way to really do what you want would to be having the data in separate columns by type. Then having within a calculated measure which returns based on the column.