cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
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
Highlighted
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
Highlighted
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
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors