时间戳

首页 > 解决方案 > How to Filter table and Limit it to 1 TOP row with Dax

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

标签： powerbidax

解决方案

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.

推荐阅读