topic Re: Find a value / LOOKUPVALUE in DAX Commands and Tips

Find a value / LOOKUPVALUE

MathieuF — Tue, 29 Dec 2020 13:16:05 GMT

Good morning all,
Not finding a solution to my problem, I try to work around the thing.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculation-between-2-dates-based-on-the-same-value/m-p/1543355#M30430

I want to display on the same line the start date (task.taken) and the end date (task.completed) of the same "task_id".
I illustrated this.

I carried out tests with in particular the LOOKUPVALUE formula that you can find in .testrecherche and .testrecherche2.

the difficulty I encounter is that the value "task-id" or the one found in "task_id_debut / fin" can be repeated. I need to have the one that comes right after (chronological order of created_at).

Thanks for your help.

Mathieu

PBIX: https://www.dropbox.com/sh/61ae2dtbmobl8tp/AAATpy0sRBMGRziU0Xe7Sy_Za?dl=0

Re: Find a value / LOOKUPVALUE

Greg_Deckler — Tue, 29 Dec 2020 14:32:45 GMT

MathieuF - So what is the expected output then?

Re: Find a value / LOOKUPVALUE

MathieuF — Tue, 29 Dec 2020 14:40:44 GMT

Hello Greg_Deckler and thank you for looking into my case.
I want to calculate the time between the start of the action and the end of the action.
Here is the result that I hope (before proceeding with the subtraction).

Mathieu

Re: Find a value / LOOKUPVALUE

Greg_Deckler — Tue, 29 Dec 2020 14:43:12 GMT

MathieuF See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

You may need to use MINX in your example.

Re: Find a value / LOOKUPVALUE

MathieuF — Tue, 29 Dec 2020 14:59:30 GMT

Greg_Deckler

I had seen your article and wanted to adapt it without success.
I tested your formula, but it didn't give the same result (I removed the subtraction to test.

.testRecherche3 =
VAR __Current = journal_forum[created_at]
VAR __PreviousDate = MINX(FILTER(journal_forum;journal_forum[created_at] < EARLIER(journal_forum[created_at]));journal_forum[created_at])

VAR __Previous = MINX(FILTER(journal_forum;journal_forum[created_at]=__PreviousDate);journal_forum[created_at])
RETURN
//__Current -
- __Previous

I tried this formula, but it only works for the first part. Not the second.
.testRecherche2 = IF(ISBLANK(journal_forum[Task_id_debut])=BLANK();CALCULATE(MIN(journal_forum[created_at]);FILTER(ALL(journal_forum);journal_forum[Task_id_fin]=EARLIER(journal_forum[Task_id_debut])));BLANK())

Mathieu

Re: Find a value / LOOKUPVALUE

Greg_Deckler — Tue, 29 Dec 2020 15:16:32 GMT

MathieuF -

You would need to include an additional filter like:

IF(ISBLANK(journal_forum[Task_id_debut])=BLANK();CALCULATE(MIN(journal_forum[created_at]);FILTER(ALL(journal_forum);journal_forum[Task_id_fin]=EARLIER(journal_forum[Task_id_debut]) && journal_forum[created_at]>EARLIER(journal_forum[created_at])));BLANK())

Re: Find a value / LOOKUPVALUE

MathieuF — Thu, 31 Dec 2020 14:17:23 GMT

Helolo Greg_Deckler

Thank you very much, thanks to you, I am reaching my goal.

There is one small problem.
Indeed, if I take the example of user_id 5be2f44514bf8a39cb988ff5 and task_id 5fcdf9e9bd0e542b263be8a8 (same with 5fcbc85e71336801d1debc25) we can see that there are interactions between (content.replied) which give empty fields. So I think EARLIER does not detect the next value.

How to cure it ?

My files: https://www.dropbox.com/sh/61ae2dtbmobl8tp/AAATpy0sRBMGRziU0Xe7Sy_Za?dl=0

Columns concerned:
task_id_debut
task_id_fin
.testrecherche2
.created_at_debut
.Measure1 / .testrecherche4

Moreover, there are surely simpler, without using so many columns.

Thank you for your precious help.

Mathieu

Re: Find a value / LOOKUPVALUE

MathieuF — Wed, 13 Jan 2021 09:32:37 GMT

Hello,
I'm sorry, my problem is still there.
How to make it take into account the next non empty line?
Should I use the ALLNOBLANK function? And how ?
Thanks in advance.

Greg_Deckler