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

Finding minimum value in a row with several columns

Probably a simple Question but I struggle for a while now to find a solution.

In a Table with several columns I want to find out the smallest value in a row and fill in the columns name.

In Excel quite an easy task but using PowerBI I can't find a solution.

To complicate the task I just want to select the columns name if the content is bigger than 0/null

 

 

temp_PowerBI_Anchor_Row.JPG

Any idea how to create the formula or the column "NextAnchor"?

 

Since I'm relatively new to PowerBI and PowerQuery I'd appreciate also some step-by-step approach and hope to understand and learn 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Finding minimum value in a row with several columns

HI @fishboneox

 

Please try this calculated column. replace Table1 with your Table Name

 

Column =
VAR temp = {
        ( "Anchor1", Table1[Anchor_1] ),
        ( "Anchor2", Table1[Anchor_2] ),
        ( "Anchor3", Table1[Anchor_3] ),
        ( "Anchor4", Table1[Anchor_4] ) }
VAR MinValue =
    MINX ( FILTER ( temp, [Value2] <> 0 ), [Value2] )
RETURN
    MINX ( FILTER ( temp, [Value2] = MinValue ), [Value1] )
Try my new Power BI game Cross the River

View solution in original post

4 REPLIES 4
Super User III
Super User III

Re: Finding minimum value in a row with several columns

HI @fishboneox

 

Please try this calculated column. replace Table1 with your Table Name

 

Column =
VAR temp = {
        ( "Anchor1", Table1[Anchor_1] ),
        ( "Anchor2", Table1[Anchor_2] ),
        ( "Anchor3", Table1[Anchor_3] ),
        ( "Anchor4", Table1[Anchor_4] ) }
VAR MinValue =
    MINX ( FILTER ( temp, [Value2] <> 0 ), [Value2] )
RETURN
    MINX ( FILTER ( temp, [Value2] = MinValue ), [Value1] )
Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: Finding minimum value in a row with several columns

@fishboneox

 

if there are 2 or more columns that meet the criteria and you want to get the names of both... then you can use this formula Smiley Tongue

 

Column 2 =
VAR temp = {
        ( "Anchor1", Table1[Anchor_1] ),
        ( "Anchor2", Table1[Anchor_2] ),
        ( "Anchor3", Table1[Anchor_3] ),
        ( "Anchor4", Table1[Anchor_4] ) }
VAR MinValue =
    MINX ( FILTER ( temp, [Value2] <> 0 ), [Value2] )
RETURN
    CONCATENATEX ( FILTER ( temp, [Value2] = MinValue ), [Value1], ", " )
Try my new Power BI game Cross the River
Super User III
Super User III

Re: Finding minimum value in a row with several columns

@fishboneox

 

Following post shows another way of doing the same..using combination of Power Query and DAX

 

http://www.excelnaccess.com/finding-the-column-name-with-max-or-min-value/

 

Try my new Power BI game Cross the River
fishboneox Frequent Visitor
Frequent Visitor

Re: Finding minimum value in a row with several columns

thanks a lot for your amazing answer. The provided solution works as is should.

Also thank you for the further information you have provided. 🙂

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors