topic Re: DAX code wont pick up columns despite being in the source data. in DAX Commands and Tips

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

ollie2110 — Fri, 19 Jul 2024 03:01:18 GMT

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.

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

tamerj1 — Fri, 19 Jul 2024 05:09:01 GMT

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] )

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

ollie2110 — Fri, 19 Jul 2024 05:14:32 GMT

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)

)