Producing a text entry from another table in an IF statement

[Anonymous]

Hello,

Help please! I've done this before but for the life of me I can't remember how.

I have two tables which are joined by staff name: the Master Table includes staff details and the Absence Table includes absence details. The Master Table has been expanded to include a new row to represent each month of activity. I've already produced the total number of absences in the Master Table. Now I want to produce the reason given.

This forumala is wrong because you can't sum a text field, but it demonstrates what I'm trying to do.

UL - Reason = IF('Master Table'[In Month]=DATE(2018,04,01), CALCULATE(SUM('Absence Table'[Level 1 Reason]), 'Absence Table'[In Month]=DATE(2018,04,01)),
0)

 Can anyone help? RELATED('Absence Table'[Level 1 Reason]) doesn't seem to be working...

Tiff

[jdbuchanan71]

Hello @Anonymous 

You could try using LOOKUPVALUE like so:

UL - Reason = LOOKUPVALUE ( 'Absence Table'[Level 1 Reason], 'Absence Table'[In Month], 'Master Table'[In Month], 'Absence Table'[Staff Name], 'Master Table'[Staff Name] )

[Anonymous]

Ah yes that's ringing bells. This is saying a table of multiple values was selected, though...

[Anonymous]

Actually, that's probably because someone's had more than one instance (and therefore more than one reason) for an absence in a month, isn't it? If that's the case this isn't going to work...

[sathiyen · Accepted solution]

you can concatenate the reasons in a single column (as comma seperated) and use the same lookup function to get that column value.

This Will give the name of the employees and the reasons (comma separeted values).