How to Filter table and Limit it to 1 TOP row with Dax

Question

I'm new to Power BI and Dax queries. I have 2 tables added with 1:* relation. What I'm trying to do is to filter the second table by a foreign key and specific Key.

Example:

###Table1:###
ID | RunID | ...[Other columns]

###Table2:###
ID | RunID | Key | Value | State

So what I'm trying to do in Power BI using Dax query is to get value for a key. (It's possible to have duplicated Key-Value pairs but I can take a top one)

NEWCOLUMN =
VAR tmp =
    FILTER (
        Table2,
        AND ( Table2[RunID] = Table1[RunID], Table2[Key] = "KEY_I_NEED" )
    )
VAR tmp2 =
    CALCULATE ( VALUES ( Table2[Value] ), TOPN ( 1, tmp ) )
RETURN
    tmp2

Right now I'm getting the error:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

and couldn't get it to work the way I need.

Are you trying to add a column to Table1 or are you trying to create a new table or something else? — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2019-04-23T01:57:35

If there is only one distinct value (duplicates are fine) then you can use a lookup:

NEWCOLUMN =
    LOOKUPVALUE(
        Table2[Value],
        Table2[RunID], Table1[RunID],
        Table2[Key], "KEY_I_NEED"
    )

If there are multiple distinct values, then you can use a max:

NEWCOLUMN =
    CALCULATE(
        MAX( Table2[Value] ),
        Table2[Key] = "KEY_I_NEED"
    )

The above implicitly matches RunID (assuming that's the columns the tables are related on).

There are plenty of other ways you could do this using various combinations of functions like TOPN, MAXX, RELATEDTABLE, VALUES, and so forth, but these aren't likely to be simpler or more efficient than what I've suggested.

How to Filter table and Limit it to 1 TOP row with Dax

1 Answers