Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, please help to write an appropriate DAX code.
The issue:
A transaction is a row that contains a Driver Number, Vehicle Number, Load Date and a Load Time.
Upon checking the fact table, if a transaction for the same driver number, same load date, and load time in the time range of an hour (before or after the current load time) is found, then return the previous vehicle number, else return blank.
Thanks!
Solved! Go to Solution.
Hey @mish_1703 , how about this:
Output =
VAR CALC =
CALCULATE (
MAX ( 'Table'[Load Time] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Load Date], 'Table'[Driver Number] ),
EARLIER ( 'Table'[Load Time] ) - 'Table'[Load Time]
)
)
VAR PreviousRow =
TOPN (
1,
FILTER (
'Table',
'Table'[Vehicle Number] < EARLIER ( 'Table'[Vehicle Number] )
),
'Table'[Vehicle Number], DESC
)
VAR PreviousValue =
MINX ( PreviousRow, 'Table'[Vehicle Number] )
RETURN
IF (
ISBLANK ( CALC ),
"BLANK",
IF (
AND (
DATEDIFF ( 'Table'[Load Time], CALC, MINUTE ) < 60,
DATEDIFF ( 'Table'[Load Time], CALC, MINUTE ) > -60
),
PreviousValue,
"BLANK"
)
)
Proud to be a Super User!
Hey @mish_1703 , how about this:
Output =
VAR CALC =
CALCULATE (
MAX ( 'Table'[Load Time] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Load Date], 'Table'[Driver Number] ),
EARLIER ( 'Table'[Load Time] ) - 'Table'[Load Time]
)
)
VAR PreviousRow =
TOPN (
1,
FILTER (
'Table',
'Table'[Vehicle Number] < EARLIER ( 'Table'[Vehicle Number] )
),
'Table'[Vehicle Number], DESC
)
VAR PreviousValue =
MINX ( PreviousRow, 'Table'[Vehicle Number] )
RETURN
IF (
ISBLANK ( CALC ),
"BLANK",
IF (
AND (
DATEDIFF ( 'Table'[Load Time], CALC, MINUTE ) < 60,
DATEDIFF ( 'Table'[Load Time], CALC, MINUTE ) > -60
),
PreviousValue,
"BLANK"
)
)
Proud to be a Super User!
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |