cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Related table last occurance

I am fairly new to power bi, but experienced in MSAccess vba. This project has been a bit of a challenge for me.

 

I have 2 tables, Contracts & Tickets. I am trying to find the contract number with a start date that was just prior to the ticket opened date.

 

 

Contracts:

Itm

Contract

StartDt

EndDt

Aaa

123

12/1/18

12/3/18

BBB

124

12/1/18

12/4/18

BBB

126

12/5/18

12/12/18

Aaa

128

12/6/18

12/10/18

 

Ticket:

TickNbr

Itm

Opened

5678

Aaa

12/4/18

5679

Aaa

12/7/18

5680

BBB

11/30/18

 

Results:

TickNbr

Itm

Contract

5678

Aaa

123

5679

Aaa

128

5680

BBB

Null

 

Any help would be appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Related table last occurance

@Anonymous

 

Try this as a calculated column in Ticket Table

 

Calc Column in Ticket Table =
MINX (
    TOPN (
        1,
        FILTER (
            Contracts,
            Contracts[Itm] = Ticket[Itm]
                && Contracts[StartDt] < Ticket[Opened]
        ),
        Contracts[StartDt], DESC
    ),
    [Contract]
)
2 REPLIES 2
Super User
Super User

Re: Related table last occurance

@Anonymous

 

Try this as a calculated column in Ticket Table

 

Calc Column in Ticket Table =
MINX (
    TOPN (
        1,
        FILTER (
            Contracts,
            Contracts[Itm] = Ticket[Itm]
                && Contracts[StartDt] < Ticket[Opened]
        ),
        Contracts[StartDt], DESC
    ),
    [Contract]
)
Anonymous
Not applicable

Re: Related table last occurance

Thank you! Worked perfect!