Forum Discussion

bonjourposte's avatar
bonjourposte
Icon for Helper V rankHelper V
2 years ago
Solved

DAX help with FILTER(RELATEDTABLE)

I have two tables with an active one-to-many relationship: the loan table (LOANMAST) and the interested party table (LOAN_IPT).

LOANMAST is on the "one" side, and LOAN_IPT is on the "many" side, because there are many interested parties associated with each loan (brokers, guarantors, appraisor, etc.).  

 

 

 

I need a calculated column on LOANMAST that shows me not just the broker for that loan, but the current broker.  (ie. brokers come and go from companies, and when a broker is replaced with another one, we put an end date for the one who left, and enter a new line for the new broker.  The end date for the new broker is "null".)  So one loan can have more than one line of broker.

 

The column in which the actual name of the broker is found is SHORT_NAME.

 

So I'm putting my calculation on the LOANMAST table, and I'm thinking it should be something like:

Current Broker = FILTER(RELATEDTABLE(LOAN_IPT), LOAN_IPT[SHORT_NAME], LOAN_IPT[INTEREST] = "BROKER") AND 'END_DATE' = "null"

 

Clearly I don't know how to phrase the formula.  Please help.  Thanks!

 

(*It cannot be a measure, it has to be a column.)

Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    bonjourposte try this 

     

    Current Broker Column = 
MAXX ( 
    FILTER(
        RELATEDTABLE(LOAN_IPT), 
        LOAN_IPT[INTEREST] = "BROKER" && 'END_DATE' = "null"
    ),
    LOAN_IPT[SHORT_NAME]
)
  • bonjourposte's avatar
    bonjourposte
    2 years ago

    So, I figured it out. 

     

    I tried what you gave me, but I got this error:

     

    I realized it doesn't like text coming from a date column.  This is what my END_DATE column looked like:

     

     

    So I changed the DAX slightly to replace

    "&& LOAN_IPT 'END_DATE'='null'"

    to

    "&& ISBLANK(LOAN_IPT[END_DATE]": 

     
    BROKER NEW COLUMN = MAXX(FILTER(RELATEDTABLE(LOAN_IPT),LOAN_IPT[INTEREST] = "BROKER" && ISBLANK(LOAN_IPT[END_DATE])),LOAN_IPT[SHORT_NAME])
     
    And it worked!!  THANKS SO MUCH!!

     

     

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

    bonjourposte try this 

     

    Current Broker Column = 
MAXX ( 
    FILTER(
        RELATEDTABLE(LOAN_IPT), 
        LOAN_IPT[INTEREST] = "BROKER" && 'END_DATE' = "null"
    ),
    LOAN_IPT[SHORT_NAME]
)
    • bonjourposte's avatar
      bonjourposte
      Icon for Helper V rankHelper V
      2 years ago

      Thanks so much, but it doesn't like MAXX because it's only used to return a number.  What would be the text equivalent of MAXX?

    • bonjourposte's avatar
      bonjourposte
      Icon for Helper V rankHelper V
      2 years ago

      So, I figured it out. 

       

      I tried what you gave me, but I got this error:

       

      I realized it doesn't like text coming from a date column.  This is what my END_DATE column looked like:

       

       

      So I changed the DAX slightly to replace

      "&& LOAN_IPT 'END_DATE'='null'"

      to

      "&& ISBLANK(LOAN_IPT[END_DATE]": 

       
      BROKER NEW COLUMN = MAXX(FILTER(RELATEDTABLE(LOAN_IPT),LOAN_IPT[INTEREST] = "BROKER" && ISBLANK(LOAN_IPT[END_DATE])),LOAN_IPT[SHORT_NAME])
       
      And it worked!!  THANKS SO MUCH!!