Forum Discussion
DAX column that returns values from column when data points are >=1
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())
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:
AVERAGE(table[numbers]),
SUM(table[numbers]),
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!
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
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())
Of course! Simple enough. That worked and all my numbers are what i need them to be. THANK YOU!