Forum Discussion

MathieuF's avatar
MathieuF
Icon for Helper III rankHelper III
5 years ago

Find a value / LOOKUPVALUE

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-...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

MathieuF - 

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

 

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())
MathieuF's avatar
MathieuF
Icon for Helper III rankHelper III
5 years ago

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