# need inputs for a DAX formula

• ### 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

• Make a table with the String Col and a numeric column fro 1-26.
• Marked as answer by 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 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 Tuesday, January 31, 2012 8:49 PM
Monday, January 30, 2012 10:52 PM

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