cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 :

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

Thanks,

Ashish

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Helper III

..

ashish

Super User

I am taken to a sign-in page.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

Thanks,

Ashish

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

Thank you for your help !

It works finally . You are a star.

Regards,

Ashish

Super User

You are welcome.  Thank you for your kind words.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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
Helper III

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

Super User

Hi,

This calculated column formula works

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

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 :

I hope i am make it clear make requirement now.

Regards,

Ashish

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors