Forum Discussion

pardeepd84's avatar
pardeepd84
Helper III
5 years ago
Solved

exclude null/blanks when subtracting

Hello,

 

I have grouped some data together which includes the following:

 

Company name

Client ID

Double up

Double up min time

Double Up max time

 

 

I am now trying to subtract the Max and Min columns but ignoring any columns that are blank/nulls in the double up min time and double up max time however my formula doesn't appear to be working.  Please could I have some assistance please. 

 

My formula is:

 

Column 1 = ABS(
(IF(ISBLANK('Doubleup test (2)'[Double up Max Time]), 'Doubleup test (2)'[Double up Max Time],
IF(ISBLANK('Doubleup test (2)'[Double up MIN Time]), 'Doubleup test (2)'[Double up MIN Time],
'[Max] - [Min]))))
 
I get an error message stating "expressions that yield variant data-type cannot be used to define calculated columns.".  I have checked the formatting for the columns and they are set to text.  
 
What I need to do it get the max-min difference but only calculate this if there is data in both the Double up Max Time and the Double up Min Time columns.  if the cells are null then I want it to return an empty cell/or do not count it.  
 
I have also tried the below but that hasn't worked either
 
IF (ISBLANK( [Double up MIN Time] ), BLANK(),
IF (ISBLANK( [Double up MIN Time] ), BLANK(),
[max] - [min]))
 

 

Need Help
Tutorial Requests
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    What if we try it like this?

    Column 1 =
ABS (
    IF (
        'Doubleup test (2)'[Double up Max Time] = ""
            || 'Doubleup test (2)'[Double up MIN Time] = "",
        BLANK (),
        'Doubleup test (2)'[Max] - 'Doubleup test (2)'[Min]
    )
)

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    What if we try it like this?

    Column 1 =
ABS (
    IF (
        'Doubleup test (2)'[Double up Max Time] = ""
            || 'Doubleup test (2)'[Double up MIN Time] = "",
        BLANK (),
        'Doubleup test (2)'[Max] - 'Doubleup test (2)'[Min]
    )
)
  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    pardeepd84 

    Give this a try, the || is the OR in DAX.

    Column 1 =
ABS (
    IF (
        ISBLANK ( 'Doubleup test (2)'[Double up Max Time] )
            || ISBLANK ( 'Doubleup test (2)'[Double up MIN Time] ),
        BLANK (),
        'Doubleup test (2)'[Max] - 'Doubleup test (2)'[Min]
    )
)
  • pardeepd84's avatar
    pardeepd84
    Helper III
    5 years ago

    Hi jdbuchanan71 ,

     

    I have tried this but it still seems to be calculating the max-min even though the double up min and double up max time columns are empty/null.  

     

     

    I have grouped my data and then created 2 new columns based on actual start time (this is from the original dataset) to get the MIN and MAX values.  What I have realised is that the MIN, MAX is still being calculated even if the 'double up MIN time' and 'double up Max TIME' are blank/null.  

     

    Is there a way to group data but then only calculate if the cells are blank.  My grouped data is as follows:

     

    Please could I have some assistance with working this out.