Forum Discussion

BWL's avatar
BWL
Icon for Helper II rankHelper II
8 years ago
Solved

Compare columns with BLANK ()

Dear users,

I was always able to manage my dax queries by reading the forum or search on the internet. But unfortunatly iam completely stuck with this DAX formula. The situation is:

I have 6 columns (A, B, C, D, E, F)

A = Ordernumber
B = Contains a value (price) or is BLANK
C = Contains a value (price) or is BLANK
D = Contains a value (price) or is BLANK
E = Contains a value (price) or is BLANK
F = Contains a value (price) or is BLANK

What I want to do in column G is compare all the values and return the lowest value and no BLANK.

Is there anybody who has some tips or who can get this fixed?

Yours faithfully,

BWL
Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    BWL

     

    Ok, in Query Editor maybe this is not easy possible.

     

    Let go to DAX

     

    Use a calculated column: 

    Column =
MINX (
    UNION (
        ROW ( "PRICE"; Table1[A] );
        ROW ( "PRICE"; Table1[B] );
        ROW ( "PRICE"; Table1[C] );
        ROW ( "PRICE"; Table1[D] );
        ROW ( "PRICE"; Table1[E] );
        ROW ( "PRICE"; Table1[F] )
    );
    [PRICE]
)

    Regards

     

    Victor

    Lima - Peru

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    8 years ago

    BWL

     

    Hi, Could be possible in Query Editor?


    Regards

     

    Victor

    • BWL's avatar
      BWL
      Icon for Helper II rankHelper II
      8 years ago
      Vvelarde thank for your reply!

      I don't know how! The values column B untill F are returned with a RELATED from different Lookup tables.
      Thats why I think it cant be done with Query Editor.
      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        8 years ago

        BWL

         

        Ok, in Query Editor maybe this is not easy possible.

         

        Let go to DAX

         

        Use a calculated column: 

        Column =
MINX (
    UNION (
        ROW ( "PRICE"; Table1[A] );
        ROW ( "PRICE"; Table1[B] );
        ROW ( "PRICE"; Table1[C] );
        ROW ( "PRICE"; Table1[D] );
        ROW ( "PRICE"; Table1[E] );
        ROW ( "PRICE"; Table1[F] )
    );
    [PRICE]
)

        Regards

         

        Victor

        Lima - Peru