Lookup of value from another table

[Anonymous]

Simple question for a relative newbie to Powerbi. Hoiping someone can help.

I have two tables which contain Revenue in table 1 and Working days in table 2

The link field  between the tables is calendar month, and the field I want to bring into table 1 is named "working days"

They are linked on a many to one relationship.

I want to return a single value against each revenue line/calendar month combination in table 1 for the corresponding value in the working days column. 

E.g. if the month is June and the number of working days is 25, then each revenue line/calendar month combination in table 1 would have 25 in the new column of the revenue table based on this lookup.

I've googled this but seen so many conflicting ways to get the right answer.

Any help much appreciated. First forum post to MS Powerbi....probably not the last.

[Anonymous · Accepted solution]

Hi Greg, really appreciate the pointers.....hopefully the above helps with the table layout and relationship.

I'm looking to bring back the value from the Working table in the ADJ column and use this to multiply by the Sales Value, and so far I have used LOOKUPVALUE (as per your suggestion) to get the following values back where there is a match (example below).

The next bit shows the result when I then multiply the two bits on a new column..

Is this the correct way to build this, and the most efficient? Or is there a better way? Thanks for the pointers....as I was list in CALCULATE/CALCULATE TABLE, LOOKUPVALUE/FILTER....all of which I am slowly learning.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calculated column as below should be ok.

Column = 
CALCULATE (
    SUM ( WORKING[ADJ] ),
    FILTER (
        WORKING,
        WORKING[Calendar Month Name] = 'NEW QV MASTER'[Calendar Month Name]
    )
) * [Sales value]

[Anonymous · Accepted solution]

Thats is perfect...thank you so much 

Is it possible for me to insert another condition in that formula to make it only apply when a financial year = YYYY

Struggling with how to include this in the logic.

[Greg_Deckler]

You probably want something like LOOKUPVALUE. Would need more info to be more specific. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous · Accepted solution]

Hi Greg, really appreciate the pointers.....hopefully the above helps with the table layout and relationship.

I'm looking to bring back the value from the Working table in the ADJ column and use this to multiply by the Sales Value, and so far I have used LOOKUPVALUE (as per your suggestion) to get the following values back where there is a match (example below).

The next bit shows the result when I then multiply the two bits on a new column..

Is this the correct way to build this, and the most efficient? Or is there a better way? Thanks for the pointers....as I was list in CALCULATE/CALCULATE TABLE, LOOKUPVALUE/FILTER....all of which I am slowly learning.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calculated column as below should be ok.

Column = 
CALCULATE (
    SUM ( WORKING[ADJ] ),
    FILTER (
        WORKING,
        WORKING[Calendar Month Name] = 'NEW QV MASTER'[Calendar Month Name]
    )
) * [Sales value]

[Anonymous · Accepted solution]

Thats is perfect...thank you so much 

Is it possible for me to insert another condition in that formula to make it only apply when a financial year = YYYY

Struggling with how to include this in the logic.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To use && instead of "," in filter. such as,

Column = 
CALCULATE (
    SUM ( WORKING[ADJ] ),
    FILTER (
        WORKING,
        WORKING[Calendar Month Name] = 'NEW QV MASTER'[Calendar Month Name] && WORKING[F Year] in {"2017-2018"} 
    )
) * [Sales value]

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

This is perfect...thank you.

I appear to have clicked on the wrong post to accept as a solution.

Really appreciate your support and time.