CODEWITHSUNDEEP
powerbidax
vandelay
vandelay

Reputation: 2075

DAX Lookupvalue with TOPN and filter

I am trying to lookup a value from another table with a filter, and a TOPN.

I have two tables like this:

Table1

ID   Latest
X-1
X-2

Table2 (Key is concatenation of ID and Start)

ID  Start    Key    Status
X-1   1     X-1 - 1   Done
X-1   2     X-1 - 2   Done
X-1   3     X-1 - 3   Open
X-1   4     X-1 - 4   Open
X-2   1     X-2 - 1   Done
X-2   2     X-2 - 2   Open
X-2   3     X-2 - 3   Open
X-2   4     X-2 - 4   Open

What I'd like to do is, for the empty values in Table1[Latest] to find the value in Table2[Key] based off the highest number in Table2[Start] and only those in Table2[Status] with "Open" respectively for each ID in Table1.

So that Table1 ends as:

ID   Latest
X-1   X-1 - 2
X-2   X-2 - 1

I thought something along the lines of 

Latest = LOOKUPVALUE(ggTwo[Key], ggTwo[ID], TOPN(1, ggTwo, CALCULATE(ALL(ggTwo[ID]), FILTER(ggTwo, ggTwo[ID] = ggOne))))

But this is refering to multiple columns. I am not sure how to tackle this one.

Upvotes: 1

Views: 4619

Answers (2)

mikeinman
mikeinman

Reputation: 31

To get the top start where the status is open, I created a calculated column on table two.

LastOpenStart = 
CALCULATE (
    MAX ( ggTwo[Start] ),
    ggTwo[Status] = "open",
    FILTER ( ggTwo, ggTwo[ID] = EARLIER ( ggTwo[ID] ) )

//Query
EVALUATE
SELECTCOLUMNS(
    NATURALINNERJOIN (
        FILTER ( ggTwo, ggTwo[LastOpenStart] = ggTwo[Start] ),
        FILTER ( ggOne, ggOne[Latest] = BLANK () )
    ),
    "ID",ggTwo[ID],
   "Key",ggTwo[Key]
)

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40244

I would use MAXX instead of TOPN. For example,

Latest = MAXX(
              FILTER(Table2,
                    Table2[Status] = "Done" &&
                    Table2[ID] = EARLIER(Table1[ID])),
              Table2[Key])

If you have a relationship based on the ID columns, then you don't need the second condition:

Latest = MAXX(FILTER(RELATEDTABLE(Table2), Table2[Status] = "Done"), Table2[Key])

Upvotes: 3

Related Questions