locked
need inputs for a DAX formula RRS feed

  • Question

  • consider

     

    TableA:

    PrimayKey

    1

    2

    3

    ....

     

    TableB:

    foreignKey StringColumn

    1                 X

    1                 Y

    2                 Y

    2                 Z

    3                 X

     

    Run DAX on Table A such that we have:

    Primarykey StringColumn

    1                X

    2                Y

    3                X

     

    So that's basically fetching a minimum value from the string column of related rows from TableB.

     

    Thanks!

    Monday, January 30, 2012 10:28 PM

Answers

  • Make a table with the String Col and a numeric column fro 1-26.
    • Marked as answer by powerpivotq Tuesday, January 31, 2012 8:49 PM
    Monday, January 30, 2012 10:52 PM
  • Nothing easy... MAX and MIN do not work with strings, and this is a pain in the neck.

    You can add an "IsFirst" column to TableB, something like:

    TableB[IsFirst] = CALCULATE (
        COUNTROWS (TableB),
        TableB[PrimaryKey] = EARLIER(TableB[PrimaryKey]), 
        TableB[StringColumn] <= EARLIER (TableB[StringColumn]),
         ALL (TableB)
    ) = 1

    This will contain TRUE only for the first string. Then you create your new column in TableA like:

    TableA[MinStringValue] = CALCULATE (VALUES(TableB[StringColumn]), TableB[IsFirst])

    Not very elegant and/or neat, but it should work fine. :)


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    • Marked as answer by powerpivotq Tuesday, January 31, 2012 8:49 PM
    Tuesday, January 31, 2012 11:45 AM

All replies

  • Make a table with the String Col and a numeric column fro 1-26.
    • Marked as answer by powerpivotq Tuesday, January 31, 2012 8:49 PM
    Monday, January 30, 2012 10:52 PM
  • Thank you for the reply. It was helpful.

    Any other alernative approach that can be taken to solve above problem?

    Tuesday, January 31, 2012 12:26 AM
  • Nothing easy... MAX and MIN do not work with strings, and this is a pain in the neck.

    You can add an "IsFirst" column to TableB, something like:

    TableB[IsFirst] = CALCULATE (
        COUNTROWS (TableB),
        TableB[PrimaryKey] = EARLIER(TableB[PrimaryKey]), 
        TableB[StringColumn] <= EARLIER (TableB[StringColumn]),
         ALL (TableB)
    ) = 1

    This will contain TRUE only for the first string. Then you create your new column in TableA like:

    TableA[MinStringValue] = CALCULATE (VALUES(TableB[StringColumn]), TableB[IsFirst])

    Not very elegant and/or neat, but it should work fine. :)


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    • Marked as answer by powerpivotq Tuesday, January 31, 2012 8:49 PM
    Tuesday, January 31, 2012 11:45 AM
  • Thank you. It helps too!
    Tuesday, January 31, 2012 8:49 PM