Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
49 | |
45 | |
20 | |
16 |