Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ashc_cool10
Helper III
Helper III

how to get Min value of text column from Earlier function

Hi Team,

 

I am stuck in one calculation in which I need to check the Minimum Value of "CHANGENR" column and populate the "VALUE_OLD" entry in front of every column .

Please see below for more calrification :

Check_12334.JPG

 

I trired using Min and Earlier function to compare these column but its giving me wrong value. Request you to please let me know what needs to be corrected in this:

 

check_1233 = CALCULATE(MIN(CV_CLIP[CDPOS.VALUE_OLD]),FILTER(CV_CLIP,CV_CLIP[CDPOS.CHANGENR]=EARLIER(CV_CLIP[CDPOS.CHANGENR])))

check_234.JPG

 

@Ashish_Mathur  

Thanks,

Ashish

 

1 ACCEPTED SOLUTION

Hi,

This calculated column formula works

Reslt = LOOKUPVALUE(Sheet1[VALUE .OLD],Sheet1[CHANGENR],CALCULATE(MIN(Sheet1[CHANGENR]),FILTER(Sheet1,Sheet1[OBJECT.ID]=EARLIER(Sheet1[OBJECT.ID]))),Sheet1[OBJECT.ID],Sheet1[OBJECT.ID])

Hope this helps.

Ashish_Mathur_0-1706873070907.png

 


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

View solution in original post

11 REPLIES 11
ashc_cool10
Helper III
Helper III

..

ashish

I am taken to a sign-in page.


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

Please check below link and try to downlaod from this

https://drive.google.com/file/d/1bdLVYS3h0FCXV_QeOAFs3Oo_iIP2n2uY/view?usp=sharing 

 

Thanks,

Ashish

Hi,

This calculated column formula works

Reslt = LOOKUPVALUE(Sheet1[VALUE .OLD],Sheet1[CHANGENR],CALCULATE(MIN(Sheet1[CHANGENR]),FILTER(Sheet1,Sheet1[OBJECT.ID]=EARLIER(Sheet1[OBJECT.ID]))),Sheet1[OBJECT.ID],Sheet1[OBJECT.ID])

Hope this helps.

Ashish_Mathur_0-1706873070907.png

 


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

Hi @Ashish_Mathur ,

 

Thank you for your help !

 

It works finally . You are a star.

 

Regards,

Ashish

You are welcome.  Thank you for your kind words.


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

Hi,

Cannot understand your question.  The entries in the CHANGENR column are text.  How can you take the minimum of a column of text entries?


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

Hi @Ashish_Mathur ,

 

You are right data type of column"CHANGENR" is Text whoever i can change it to "Whole Number" .

But i am not sure why i am getting wrong result .

 

Is there any solution how can i achieve this?

 

Thanks,

Ashish

Hi,

This calculated column formula works

Column = lookupvalue(Data[VALUE OLD],Data[CHANGENR],min(Data[CHANGENR]))

Hope this helps.

Ashish_Mathur_0-1706786926539.png

 


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

Hi @Ashish_Mathur ,

I have changed the format of "Changenr","OBJECTID"  and "Value OLD" from Text to whole  Number  and then I tried the above forumla however i am getting the same value on each entry . 

As we know Lookup function will work as VLOOKUP in which whenever there is  a first exact match of Min value in CHANGENR column it will populate the first entry each row however It should give me  if its "OBJECT ID" is different then it should  check "Min" value of that "CHANGENR" Column and should pick the "Value Old entry " .

 

To make it more clear please see below screenshot of desire result :

value_check.JPG

value_2.JPG

value_3.JPG

I hope i am make it clear make requirement now.

Request you to please help .

 

Regards,

Ashish

 Share the download link of the PBI file.


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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.