Forum Discussion
Get value from another table based on 2 conditions
Hello I am new to PowerBI and don't even know where to begin with this formula.
I have 2 tables, one is called "cr791_hourlytracking", the other is a list of invoice numbers with account and week start date as columns like so called "Table1". I created a relationship between the two tables for the account and week start date columns.
I want to create a new column in the "cr791_hourlytracking" table that pulls invoice numbers based on account and week start date. I have an account and week of column in both tables.
Table names in "cr791_hourlytracking" are
"Week Start Date"
"Account"
So I need something like
If cr791_hourlytracking[weekstartdate] = table[week of]
and cr791_hourlytracking[account] = table1[account]
then return table1[invoicenumber]
- I tried this and it didn't work. Returned blanks. Not sure whyLOOKUPVALUE(Table1[Invoice Number],Table1[Week of],cr791_hourlytracking[Week Start Date],Table1[cr791_Account],cr791_hourlytracking[Account])
3 Replies
- I tried this and it didn't work. Returned blanks. Not sure whyLOOKUPVALUE(Table1[Invoice Number],Table1[Week of],cr791_hourlytracking[Week Start Date],Table1[cr791_Account],cr791_hourlytracking[Account])
That seems like it should work unless there are multiple Invoice Number values for a given week and account.
Can you verify that both week columns are date type and the dates do align like you expect?
Yeah that fixed it. I guess when I made the list of invoice numbers and dates I messed up. I cleaned it up and the formula worked.
