CODEWITHSUNDEEP
powerbidax
KReEd
KReEd

Reputation: 368

Filter out string and numbers based on certain condition in DAX Power BI

I have my data -

sl.no category quantity
1 yes 13
2 no unanswered
3 no 10
4 yes 15
5 no 17
6 no unanswered
7 no 9
8 yes 0

now i want to create a new column(#quant) where it should be - if in category column the number is greater than 15 it should be ">15", if less than equal to 15 then "<=15", if 0 then "0", and unanswered should be left as it is. So my final output will be -

sl.no  category   quantity      #quant(newly created column)
1        yes      13            <=15
2        no       unanswered    unanswered
3        no       10            <=15
4        yes      15            <=15
5        no       17            >15
6        no       unanswered    unanswered
7        no       9             <=15
8        yes      0              0

please help me to get the desired output

Upvotes: 0

Views: 2378

Answers (2)

Simon Noer Petersen
Simon Noer Petersen

Reputation: 129

if you want it as a dax Column.

#quant(newly created column) =
IFERROR (
    SWITCH (
        TRUE (),
        VALUE ( 'Table (2)'[quantity] ) = 0, "0",
        VALUE ( 'Table (2)'[quantity] ) > 15, ">15",
        VALUE ( 'Table (2)'[quantity] ) <= 15, "<=15"
    ),
    'Table (2)'[quantity]
)

this only takes cells that contain strings that can be converted as values. if thats not possible, it returns the cell content.

Upvotes: 1

Andrey Nikolov
Andrey Nikolov

Reputation: 13440

You can add a custom column using this formula

if ([quantity] = "unanswered" or [quantity] = "0") then 
  [quantity]
else
  try
    if Int32.From([quantity]) > 15 then ">15"
    else "<=15"
  otherwise "not a number"

Like this

enter image description here

Which will give you the expected result:

enter image description here

It will check if quantity is unanswered or 0 and leave it unchanged in this case. Otherwise, it will try to convert the value to a whole number and check is it more than 15 or not. In case it is not a number, it will return not a number.

Upvotes: 2

Related Questions