Alternative for RELATED function - Unable to connect tables (many to one address)

[DevadathanK]

Hello everyone

<Message removed>

Thanks for any help!

[v-diye-msft]

Hi @DevadathanK 

Please kindly let me know if you'd like to get below results:

Measure for the number of days the person is on leave in week1:

Measure = CALCULATE(SUM('Table (2)'[Days]),FILTER(ALL('Table (2)'),[To Week No.]=1&&[From Week No. ]=1),VALUES('Table (2)'[Employee ID]))

 Calculated column for Week 1 Efficiency

Week 1 Efficiency = DIVIDE([Week1],[Hours])*(5-[Measure])

Pbix attached.

[DevadathanK]

Hey @v-diye-msft 

Thank you very much for trying to help me out! 

<Message deleted>

I shall try out some other calculation algorithm, using the Measure functionality as you suggested.

[v-diye-msft · Accepted solution]

Hi @DevadathanK 

Kindly check this one?

Week 1 Efficiency = DIVIDE([Week1],[Hours]*(5-[Measure]))

 

Sample attached.

[DevadathanK]

This works out well @v-diye-msft Thank you! 

However when the Leaves applied become more complex, spanning multiple weeks, it brings out other challenges. 

Since you have answered this post, i have made a separate post for addressing the other issues.

Please have a look if you get time! 

https://community.powerbi.com/t5/Desktop/Unable-to-create-a-column-by-looking-up-values-in-a-another...

Thanks again!

[Greg_Deckler]

So, try using RELATEDTABLE instead of RELATED. You should be able to do something like FILTER(RELATEDTABLE(...)...)