cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamesCarter
Frequent Visitor

Dax Measure to return a single value from a column when using Min function on a different column

Hi
I need to create a measure (and it has to be a measure, not a column, due wider reports/ calculations/ interdependencies). I need that measure to return the Ref which relates to the first start date. In the event that the first start date occurs twice or more, I need it to return the first occurrence. 

 

Example data is below:

RefStart Date
12345601/01/2021
78912303/04/2021
45601909/05/2021
83921312/05/2021
12745901/01/2021
29302227/02/2021
93485913/02/2021
29485015/04/2021
93057318/04/2021
293850229/05/2021
75439223/03/2021

 

So, from my data above, the first start date is 01/01/21 - that occurs with refs 123456 and 127459, so I'd want the answer to be 123456. 


I can obviously get a measure which lists the first start date using Min, but beyond that I'm hitting a wall.

Would be very grateful for any advice.
Thanks

2 ACCEPTED SOLUTIONS
parry2k
Super User III
Super User III

@JamesCarter try this meaure

 

Top Ref = CALCULATE ( MIN ( Ref[Ref] ), TOPN ( 1, ALLSELECTED ( Ref ), Ref[Start Date], ASC ) )

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Ashish_Mathur
Super User III
Super User III

Hi,

Try these measures

First date = MIN(Data[Start Date])
Measure = FIRSTNONBLANK ( TOPN ( 1, VALUES (Data[Ref]), [First date],ASC ), 1 )

Drag the second measure to a card visual.  The result will be 123456.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User III
Super User III

Hi,

Try these measures

First date = MIN(Data[Start Date])
Measure = FIRSTNONBLANK ( TOPN ( 1, VALUES (Data[Ref]), [First date],ASC ), 1 )

Drag the second measure to a card visual.  The result will be 123456.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Actually ignore that bit about variable. 
I thought it had worked, but it hadn't - so I've reverted to what you did and that remains perfect.

Thanks again
James

Thanks Ashish, 
Absolute superstar. That worked a treat. 
I tweaked slightly to make the first measure a variable and I'm absolutely getting the result. 
Brilliant stuff - very grateful. I'd been playing with FIRSTNONBLANK, MIN and TOPN but just couldn't get it - haven't used ASC before. That's a new string to the bow!
Thanks again


You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User III
Super User III

@JamesCarter try this meaure

 

Top Ref = CALCULATE ( MIN ( Ref[Ref] ), TOPN ( 1, ALLSELECTED ( Ref ), Ref[Start Date], ASC ) )

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Hi @parry2k 
I really should properly wait to investigate before replying - lesson for the future.
I've found what I did wrong in the previous attempt and yes, absolutely, your solution worked.
Thanks again

Hi @parry2k 
Thanks so much for responding and for your proposal. 
I couldn't quite get it to work (it wouldn't let me call the start date). Sure it's something I did wrong in translating the measure to my actual table. 
Happily @Ashish_Mathur 's solution worked, but I'm really grateful for your time and offer of help.
I do love the support people offer on these forums.
Thanks again
James

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors