Forum Discussion

hassanh_2's avatar
hassanh_2
New Member
3 years ago
Solved

DAX Function to Create a Column Based on condition / filter in another column

Hello,

Im trying to create a new column that returns a value from another table based on condition / filter.

 

As you see I have the following 2 tables. I want to create a new column in Table 2, in that column I need to return the "Sales Person" of the first Log_ID.

 

(Note: in my relationship model there is an Inactive relationship by "Acct Nr.") 

 

 

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi hassanh_2 
    Please try

    NewColumn =
MAXX (
    TOPN (
        1,
        CALCULATETABLE ( Table2, USERELATIONSHIP ( Table1[Acct Nr], Table2[Acct Nr] ) ),
        Table2[Log_ID], ASC
    ),
    Table2[Sales Person]
)

5 Replies

  • hassanh_2's avatar
    hassanh_2
    New Member
    3 years ago

    I created the same function as a measure and it worked. Thank you!

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi hassanh_2 
    Please try

    NewColumn =
MAXX (
    TOPN (
        1,
        CALCULATETABLE ( Table2, USERELATIONSHIP ( Table1[Acct Nr], Table2[Acct Nr] ) ),
        Table2[Log_ID], ASC
    ),
    Table2[Sales Person]
)
    • hassanh_2's avatar
      hassanh_2
      New Member
      3 years ago

      Hi, Thanks!

      It seems you switched the Table Numbers but I tried both ways and the new column didnt return any value!

  • hassanh_2's avatar
    hassanh_2
    New Member
    3 years ago

    Is there a way though to create it as a Column so I can use it as a Parameter dimension in other visuals?

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      hassanh_2 

      What is the direction of the relationship between the two tables? Can you provide a screenshot?