CODEWITHSUNDEEP
powerbidax
captainjack42
captainjack42

Reputation: 25

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

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.

Upvotes: 1

Views: 2877

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40214

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.

Upvotes: 0

Related Questions