cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

get value from min timestamp by two other filters

Hi, I have a issue where I'm trying to get the value(decimal number) as my output in a measure, but it needs to filter to MIN timestamp of that task and another filter. How can I get this?
min timestamp value of minutesleft = CALCULATE(SUM(Table[minutesleft of workday]), Table[tasknumber] = 72, Table[isMain] = TRUE(), FILTER(Table, Table[originalTimeStamp] = MIN(Ark2[originalTimeStamp])))

I've tried this, but if there are values before what I would like (e.g different tasknumber) my measure returns blank.
Any good ideas on how to get just this one value. I would like to not create a calculated column to get the answer for this.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @PBISea 

I think your filter is:

Ark2[tasknumber] = 72
Ark2[isMain] = TRUE ()
AND Ark2[originalTimeStamp] = Min Date (Ark2[tasknumber] = 72 ,Ark2[isMain] = TRUE ())
In your measure you use 
FILTER(Table, Table[originalTimeStamp] = MIN(Ark2[originalTimeStamp])))
It will return the table that originalTimeStamp = 2019/5/20/ 12:03:00, so you will get blank result.
amitchandak  's measure has the same problem.
I update your measure, or you can try my measure.
Measure1:
min timestamp value of minutesleft =
CALCULATE (
SUM ( Ark2[minutesleft of workday] ),
Ark2[tasknumber] = 72,
Ark2[isMain] = TRUE (),
FILTER (
ALLSELECTED ( Ark2 ),
Ark2[originalTimeStamp] = MINX(FILTER(Ark2,Ark2[tasknumber] = MAX ( Ark2[tasknumber] )), Ark2[originalTimeStamp] )
)
)
Measure2:
Measure =
CALCULATE (
SUM ( Ark2[minutesleft of workday] ),
FILTER (
ALL ( Ark2 ),
Ark2[tasknumber] = 72
&& Ark2[isMain] = TRUE ()
&& Ark2[originalTimeStamp]
= MINX (
FILTER ( Ark2, Ark2[tasknumber] = MAX ( Ark2[tasknumber] ) ),
Ark2[originalTimeStamp]
)
)
)

The result returns to 292.52.

You can download the pbix file from this link: get value from min timestamp by two other filters

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

@PBISea , Try like

CALCULATE(SUM(Table[minutesleft of workday]), Table[tasknumber] = 72, Table[isMain] = TRUE(),
FILTER(allselcted(Table), Table[originalTimeStamp] = MIN(Ark2[originalTimeStamp]) && Table[tasknumber] =max(Table[tasknumber])))

 

or


CALCULATE(SUM(Table[minutesleft of workday]), Table[tasknumber] = 72, Table[isMain] = TRUE(),
FILTER(allselcted(Table), Table[originalTimeStamp] = MIN(Ark2[originalTimeStamp])), allexcept(Table,Table[tasknumber] ))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak 

 

Seems that both are still returning blank. I might be doing something wrong here, but seems strange.

I've attached my pbix file here 

(https://gofile.io/d/XkkSXm)

 

Thanks!

Community Support
Community Support

Hi @PBISea 

I think your filter is:

Ark2[tasknumber] = 72
Ark2[isMain] = TRUE ()
AND Ark2[originalTimeStamp] = Min Date (Ark2[tasknumber] = 72 ,Ark2[isMain] = TRUE ())
In your measure you use 
FILTER(Table, Table[originalTimeStamp] = MIN(Ark2[originalTimeStamp])))
It will return the table that originalTimeStamp = 2019/5/20/ 12:03:00, so you will get blank result.
amitchandak  's measure has the same problem.
I update your measure, or you can try my measure.
Measure1:
min timestamp value of minutesleft =
CALCULATE (
SUM ( Ark2[minutesleft of workday] ),
Ark2[tasknumber] = 72,
Ark2[isMain] = TRUE (),
FILTER (
ALLSELECTED ( Ark2 ),
Ark2[originalTimeStamp] = MINX(FILTER(Ark2,Ark2[tasknumber] = MAX ( Ark2[tasknumber] )), Ark2[originalTimeStamp] )
)
)
Measure2:
Measure =
CALCULATE (
SUM ( Ark2[minutesleft of workday] ),
FILTER (
ALL ( Ark2 ),
Ark2[tasknumber] = 72
&& Ark2[isMain] = TRUE ()
&& Ark2[originalTimeStamp]
= MINX (
FILTER ( Ark2, Ark2[tasknumber] = MAX ( Ark2[tasknumber] ) ),
Ark2[originalTimeStamp]
)
)
)

The result returns to 292.52.

You can download the pbix file from this link: get value from min timestamp by two other filters

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

Hi @RicoZhou ,

 

That looks really good and works, however, when I tried adding more ID's it stopped working correctly and gave something else in a table I created. I would like to compare different ID's and this is an issue then. Looks to be working when i filter for only on ID. 

 

Added the updated file here: https://gofile.io/d/xSlCdE

 

Really appreciate the help!

Community Support
Community Support

Hi @PBISea 

I try to download the pbix file you shared with me, but there may be something wrong in Gofile.

It will always show me :To download this file, please visit the download page : https://gofile.io/d/xSlCdE

Could you share me with your pbix file by your OneDrive for Business?

And you can give me some Screenshot of your issue and show me what result you want.

That may make it easier for me to understand your requirement.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Community Support
Community Support

Hi @Paulyeo11 

Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,

Rico Zhou

Hi, 

Sorry for my late response. Yes, problem has been solved by creating calculated columns as this proved to be needed to create other measures going forward after this as well. 

Thanks!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors