Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Expressions that yield variant data-type cannot be used to define calculated columns.

Hey Guys
 
BIZ DATE = if(and(Format([ATD],"")=blank(),format([BIZ DAYS],"")=blank()),"",
var dateidx = calculate( max('Calendar'[WorkingDayIndex] ) , 'P FILES'[ATD] = RELATED('Calendar'[Date] ) )
var newdateidx = dateidx + 'P FILES'[BIZ DAYS]

 

return
calculate(
max('Calendar'[Date]),
filter(
ALL('Calendar'),
'Calendar'[WorkingDayIndex] = newdateidx
&& 'Calendar'[IsWorkDay] = 1
)
)
)
 
Data is company sensetive.
 
I found a forum that suggested creating a calendar table to add days to a date and that seems to work (everything after the first line). However, the if statement will not seem to work. I understand that I am looking at two columns with different formatting (one is date and the other is text) I have tried using several combinations of value and format functions to no avail. I cannot change the formatting of these columns as their formatting is necessary for other calculated columns. I have tried making helper columns a little, but haven't had much luck.
 
Also, the ATD column, which is date format, has blanks (null) in it.
 
Any suggestions are appreciated.
 
-Tad
data
DAX
Dax Help
New Help

1 Reply

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi Anonymous,

    what is your desired outcome when 'P FILES'[ATD] and 'P FILES'[BIZ DAYS] are both blank? Because as it is written now, dateidx does not get a value when they are both blank.

     

    You could try something like this

    BIZ DATE =
VAR dateidx =
    IF (
        AND ( ISBLANK ( [ATD] ), ISBLANK ( [BIZ DAYS] ) ),
        BLANK (),
        CALCULATE (
            MAX ( 'Calendar'[WorkingDayIndex] ),
            'P FILES'[ATD] = RELATED ( 'Calendar'[Date] )
        )
    )
VAR newdateidx = dateidx + 'P FILES'[BIZ DAYS]
RETURN
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[WorkingDayIndex] = newdateidx
                && 'Calendar'[IsWorkDay] = 1
        )
    )