Forum Discussion

ollie2110's avatar
ollie2110
Frequent Visitor
2 years ago

DAX code wont pick up columns despite being in the source data.

I'm creating a DAX measure which calculates whether email response times were within bussiness hours. The first section is this, 

SameDay = IF(
    FORMAT([Received Date], "YYYY-MM-DD") = FORMAT([First Itinerary Date], "YYYY-MM-DD"),
    TRUE,
    FALSE
)

However the code wont find the listed columns "Received Date" or "First Itinerary Date" despite both being in the source data. 

 

 

 

DAX Issues

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi ollie2110 
    This can be be created as a calculated column

    SameDay =
FORMAT ( 'Table'[Received Date], "YYYY-MM-DD" ) = FORMAT ( 'Table'[First Itinerary Date], "YYYY-MM-DD" )

    Measures don't have direct access to the row context. It depends on what are you trying to accomplish, but in general, in order to get access to row context when creating measures you need first to create it. For example if you wish to have it as a filter you can write

    Measure2 =
CALCULATE (
    [Measure1],
    -- or expression
    FILTER (
        'Table',
        FORMAT ( 'Table'[Received Date], "YYYY-MM-DD" )
            = FORMAT ( 'Table'[First Itinerary Date], "YYYY-MM-DD" )
    )
)

    FILTER function creates row context and thus you get access to columns. 
    Other example is using X-iterators 

    Measure2 =
SUMX (
    'Table',
    IF (
        FORMAT ( 'Table'[Received Date], "YYYY-MM-DD" )
            = FORMAT ( 'Table'[First Itinerary Date], "YYYY-MM-DD" ),
        'Table'[Column]
    )
)

    Or more efficiently

    Measure2 =
SUMX (
    'Table',
    (
        FORMAT ( 'Table'[Received Date], "YYYY-MM-DD" )
            = FORMAT ( 'Table'[First Itinerary Date], "YYYY-MM-DD" )
    ) * 'Table'[Column]
)
    • ollie2110's avatar
      ollie2110
      Frequent Visitor
      2 years ago

      Hi tamerj1 

       

      Thankyou! I'm trying to create a column which calculates email response times. The issue is if they are outside of business hours the response time will calculate the total time between. So I need the column to check if the response times were on he same day and then if they weren't calculate the time between based on business hours between those two days. This is what I've got so far. 

      SameDay = IF(FORMAT([Received Date], "YYYY-MM-DD") = FORMAT([First Itinerary Date], "YYYY-MM-DD"),

          TRUE,

          FALSE

      )

       

       

      BusinessHoursStart = TIME(9, 0, 0)  // 9:00 AM

      BusinessHoursEnd = TIME(17, 0, 0)   // 5:00 PM

       

       

       

      ResponseTime =

      VAR StartTime = IF(

          HOUR([Received Date]) < 9,

          BusinessHoursStart,

          IF(HOUR([Received Date]) > 17, BusinessHoursEnd, [Received Date])

      )

      VAR EndTime = IF(

          HOUR([First Itinerary Date]) < 9,

          BusinessHoursStart,

          IF(HOUR([First Itinerary Date]) > 17, BusinessHoursEnd, [First Itinerary Date])

      )

      RETURN

      IF(

          [SameDay],

          DATEDIFF(StartTime, EndTime, MINUTE),

          DATEDIFF(StartTime, BusinessHoursEnd, MINUTE) + DATEDIFF(BusinessHoursStart, EndTime, MINUTE)

      )