The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi, this is my first post here and I want to start greeting everybody here in the community!
I have this issue with a measure: I'm intending to compute the total time between two consecutive records from a fact table, so I read a bit and learned about those WINDOW functions DAX got and could help me achieving such task. But what I'm getting is the same value for both my initial and final time (EARLIEST TIME and LATEST TIME, respectively), so when I use DATEDIFF I'm always getting 0 as a result. To analize where I'm failing in my measure, decided to count the rows from this WINDOW function, which I expect is 2 cause I'm making up it from the current and previous row but returns 1 like I'm only taking account the current row.
PARTIONBY is used cause I would want to know the total time between the same type of operation in the same day and sometimes people can make a different one in between two operations of the same class.
Tried using OFFSET and INDEX, but the results are the same.
Something weird is records who got 2 rows in the WINDOW function, but returns the same time for both measures regardless.
I did the same measure in a DAX Query and it worked nicely, but don't know how to make it works properly with my measure. This is the behaviour I'm expecting.
How can I fix this?
Thank you for your help beforehand
Solved! Go to Solution.
Hi @rajendraongole1 , your solution didn't worked, but at the end I managed to figure it out the issue: just was a wrong definition of the table in the current context. Solved it by using ALLSELECTED('TABLE') in relation instead of 'TABLE'.
Thank you for your help regardless!
Hi @rajendraongole1 , your solution didn't worked, but at the end I managed to figure it out the issue: just was a wrong definition of the table in the current context. Solved it by using ALLSELECTED('TABLE') in relation instead of 'TABLE'.
Thank you for your help regardless!
Hi @ZadquielBazan - can you modify your dax function with earliest as below:
EARLIEST TIME =
VAR PrevTimeWindow =
OFFSET(-1, REL, 0, REL, 'TABLE', , DEFAULT, PARTITIONBY('TABLE'[TIPO], 'TABLE'[USR], 'TABLE'[FECHA]))
RETURN
CALCULATE(MIN('TABLE'[HORA]), PrevTimeWindow)
to find the latest time use below calculation
LATEST TIME =
VAR PrevTimeWindow =
OFFSET(-1, REL, 0, REL, 'TABLE', , DEFAULT, PARTITIONBY('TABLE'[TIPO], 'TABLE'[USR], 'TABLE'[FECHA]))
RETURN
CALCULATE(MAX('TABLE'[HORA]), PrevTimeWindow)
Once you have the "Earliest Time" and "Latest Time" working correctly, you can use DATEDIFF to compute the total time between the two , Make sure the window function captures both the current and the previous row.
TIME_DIFF =
DATEDIFF([EARLIEST TIME], [LATEST TIME], SECOND)
Hope this works
Proud to be a Super User! | |