Looking up values in another table with 2 parameters and no unique data or key

[Anonymous]

Hello,

I am brand new to Power BI with very limited knowledge in DAX, please be patient 🙂

I have two tables where I'm trying to pull values from one table to the other to perform a calculation. Both table 1 and table 2 have a date column and another column with values in common (but not unique, neither tables have keys or are unique).

Below is an example - please note that the Dollars (table 1) and Net (table 2) are aggregated/summed up and grouped - original data contains more details.

The desired output is a column that would act identically to an Excel lookup where it would look up the net in table 2 and returns it to table 1 for further calculation based on both the date and the lookup code. Even if new columns were to be added, the lookup would return identical values based on the 2 parameters.

I have attempted a join but it seems that Power BI doesn't allow for more than 1 active join.

Table 1 (Main table)

Lookup Code	Irrelevant Data Column	Dollars
ABCD	5646541	$    1,323.00
ABCD	3245361	$  87,689.00
ABCD	6354	$  73,160.00
EFGH	321	$    7,863.00
EFGH	35435213	$  50,507.00
IJKL	6354132	$  37,299.00
IJKL	3541321	$  46,286.00
IJKL	354321	$    5,081.00
IJKL	3541	$  64,515.00
IJKL	53	$  87,324.00

Table 2

Lookup	Net
ABCD	50
EFGH	25
IJKL	250

Desired Output

Lookup Code	Irrelevant Data Column	Dollars	Lookup Code 2 (identical to Excel vlookup)	Dollars Div by Lookup Code 2
ABCD	5646541	$    1,323.00	50	$                                              26.46
ABCD	3245361	$  87,689.00	50	$                                        1,753.78
ABCD	6354	$  73,160.00	50	$                                        1,463.20
EFGH	321	$    7,863.00	25	$                                           314.52
EFGH	35435213	$  50,507.00	25	$                                        2,020.28
IJKL	6354132	$  37,299.00	250	$                                           149.20
IJKL	3541321	$  46,286.00	250	$                                           185.14
IJKL	354321	$    5,081.00	250	$                                              20.32
IJKL	3541	$  64,515.00	250	$                                           258.06
IJKL	53	$  87,324.00	250	$                                           349.30

Any help is appreciated.

Thank you,

Jake

[Anonymous]

Thank you everyone for your support!

[v-lili6-msft]

hi, @Anonymous 

you could try this simple way as below:

Step1:

Create a relationship between table1 and table2 by Lookup column and set cross filter direction is "Both"

Step2:

Use this formula to add a new column in table 1

Column = Table1[Dollars]/RELATED(Table2[Net])

or use this formula to add a new measure

Measure = SUMX(Table1,CALCULATE(SUM(Table1[Dollars]))/CALCULATE(SUM(Table2[Net])))

Result:

and here is pbix file, please try it.

Best Regards,
Lin

[Anonymous]

Hi @v-lili6-msft 

I looked at the file and it works. But for some reason when I try to apply th same relationship to my file I get the error "The cardinality you selected isn't valid for this relationship"

It might be due to the date field. As I mentioned previously, there are date fields in the raw data that were not included in the original data set. Please see below for the tables with dates. I tried to do a relationship for both date and lookup fields but it wouldn't allow it. 

Table 1

Lookup CodeDateIrrelevant Data ColumnDollars

ABCD	01/01/2018	5646541	1323
ABCD	01/01/2018	3245361	87689
ABCD	01/01/2018	6354	73160
EFGH	01/01/2018	321	7863
EFGH	01/01/2018	35435213	50507
IJKL	01/01/2018	6354132	37299
IJKL	01/01/2018	3541321	46286
IJKL	01/01/2018	354321	5081
IJKL	01/01/2018	3541	64515
IJKL	01/01/2018	53	87324
ABCD	02/01/2018	54	58995
ABCD	02/01/2018	96847	59465
ABCD	02/01/2018	365149	17664
EFGH	02/01/2018	9687	14643
EFGH	02/01/2018	6354	59661
IJKL	02/01/2018	64	16468
IJKL	02/01/2018	69874	72238
IJKL	02/01/2018	36521	51818
IJKL	02/01/2018	321	16266
IJKL	02/01/2018	321	26069

Table 2

LookupNetDate

ABCD	50	01/01/2018
EFGH	25	01/01/2018
IJKL	250	01/01/2018
ABCD	54	02/01/2018
EFGH	30	02/01/2018
IJKL	260	02/01/2018

Thank you,

Jake

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

For you different, you have same lookup code, the relationship should be many to many.

And for date slicer from two tables, its type should be list, and create a column instead of measure. 

Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Best Regards,
Lin

[Anonymous]

@Anonymous ,

First off, welcome to the wonderful and wonderfully aggravating world of PBI 🙂

Not sure if you are aware, but there's another tool in PBI which is Power Query.  You can use that to merge the lookup table into your maintable and then add a column to divide it out

1. Merge the MainTable with the Lookup table:

Expand the Lookup Table to get the Net:

Add a custom column

Final Table:

[Anonymous]

Hi Nick,

Thank you for your reply, this is great but the issue is that when I pull this data into the main dashboard (not sure of terminology is correct) it sums up the Net. I was able to make the merge on both date and lookup code column but it pulls the sume into the dashboard table.

Is there a way to just copy the pre-aggregated value into the 1st table to apply the division or does it have to be done in Power Query? Please keep in mind that table 1 will have filters and columns will be added and removed constantly, the lookup needs to be the same value as in table 2.

Please see below:

Current output with merged query:

[Anonymous]

Sure, you can do this in DAX as well.  You can bring in the NET using lookup:

Net, Using LOOKUP = 
IF( 
    HASONEFILTER(Table2[Lookup]),
    LOOKUPVALUE( 
        Table2[Net], 
        Table2[Lookup], 
        VALUES(Table2[Lookup])
    )
)

Then you can divide that out:

Total Dollars = SUM ( Table1[Dollars] )

Total Dollars / Net = 
SUMX( 
    VALUES( Table2[Lookup] ), //SUMX and VALUES just ensures you get the correct grand total
    DIVIDE( [Total Dollars], [Net, Using LOOKUP])
)

Use the LookUp column from Table2 on rows.  Since you should have a 1:M relationship with Table1, these rows will filter table1 to get you the correct total.  

You can add in Dates no problem, just increases the complexity a little bit, since you will have to add some sort of date logic to the lookup measure.  Which isnt terrible, assuming there are not any missing dates... but one step at a time 🙂

[Anonymous]

When I try that formula I get an empty column for some reason. I attempted to make some changes but getting the same issue. No error comes up but the column comes out empty.

Exact steps: Fields -> Right click on table -> New Column -> pasted formula -> added field to table