DAX column that returns values from column when data points are >=1

[Antonio195754]

Hi I'm trying to create a DAX column that returns values from a column when the values are greater or equal to 1.  The column it's pulling from has values that range from -350 through 250 and I only want to return values >=1.  

[ryan_mayu]

@Antonio195754 

is this what you want?

Measure = 
SWITCH(true(),average('Table (2)'[amount])>=1,">=1")

[Antonio195754]

hi @ryan_mayu thanks for your suggestion but that isn't what i was looking for.  My fault for  not providing more details.  The response i sent to @Anonymous provides what i'm trying to do and where i'm at with his suggestions.  Basically trying to create new column that only return data from a different column based on the specific criteria (>=1).  From there, i want to take the average of that column which would be my end goal.  

[Anonymous]

Hi @Antonio195754,

If I understand correctly, you want to check if the value of a cell in a column is greater than or equal to one, and if it is, then you want to return that value. You can create a column that returns a blank if the value is lower than 1 and the actual value of your column (in this example column "numbers") this way:

new_number_col = if(
    table[numbers]>=1,
    table[numbers],
    BLANK()

)

Depending on your use case however, it might make more sense to create a measure that returns what you are looking for. E.g. measures for the average value, or the sum of values, for all values equal to or greater to one:

avg_number = CALCULATE(
    AVERAGE(table[numbers]),

    table[numbers]>=1

)

sum_number = CALCULATE(
    SUM(table[numbers]),

    table[numbers]>=1

)

If you want to filter a table based on wheter the numbers column is lower than one without using e.g. visual-level filters you could also create a calculated column that returns a boolean value and use this helper column as a slicer/filter:

new_number_col = if(
    table[numbers]>=1,
    TRUE(),
    FALSE()

)

If I misunderstood your question and you instead want to return a value from another column based on whether the value from the numbers columns is equal to or greater than one, then you can swap out TRUE() in the expression above with the column you want to get the value from and change the FALSE() to return the value you would like.

Hope this helps, have a great weekend!

[Antonio195754]

hi @Anonymous  and thank you for your suggestions.  After reading your feedback I tried the column logic you provided and it worked to a certain extent.  What it's doing is only returning a 1 or a 0 when it meets the criteria.  it will return a 1 if it's greater or = 1, and a 0 if the number from the column is less than or =1.  In theory it's returning correctly, but what i am really looking for is what you prescribed which was to return the actual number in the column and a blank if it's outside the criteria (blank if it's not >=1 and the actual data point number if it is >=1).  What am i doing wrong?

Below is the formula i used.  Thanks in advance

Days Late by BU >= 1 = if(

NPW[Days Late HR]>=1,

NPW[Days Late HR],

BLANK()

)

[Anonymous · Accepted solution]

It looks it has to do with your column name where the operator >= 1 is interfering with the DAX [Days Late by BU >= 1]. Can you try changing it by either removing ">= 1" operator or replacing the spaces with underscores?

e.g. [Days_Late_by_BU_>=_1] or simply

[One or more days late] =

if(
NPW[Days Late HR]>=1,
NPW[Days Late HR],
BLANK()

)

[Antonio195754]

Of course!  Simple enough.  That worked and all my numbers are what i need them to be.  THANK YOU!