cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tiffany_d_nscp Regular Visitor
Regular Visitor

Producing a text entry from another table in an IF statement

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

1 ACCEPTED SOLUTION

Accepted Solutions
sathiyen Frequent Visitor
Frequent Visitor

Re: Producing a text entry from another table in an IF statement

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).

4 REPLIES 4
Super User
Super User

Re: Producing a text entry from another table in an IF statement

Hello @tiffany_d_nscp 

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] )
tiffany_d_nscp Regular Visitor
Regular Visitor

Re: Producing a text entry from another table in an IF statement

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

tiffany_d_nscp Regular Visitor
Regular Visitor

Re: Producing a text entry from another table in an IF statement

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 Frequent Visitor
Frequent Visitor

Re: Producing a text entry from another table in an IF statement

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).

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 434 members 4,425 guests
Please welcome our newest community members: