Can't get RELATED Function to work in PowerPivot

[TerryHogarth21]

I started playing around with PowerPivot but am having trouble getting the RELATED DAX function to work in a field.

There are two tables that are imported into he PowerPivot in which I concatenated 5 fields in each table to create a unique field.

Then this new field becomes the join for 2 tables, but I keep getting #ERROR when using the RELATED DAX function.

Trying to figure out if that new unique field exists when comparing one table to another.

It should be the equivalent to Excel's

 =IFERROR(VLOOKUP(A2,$E$2:$F$4,2,0),"Not Exist")

Am I going about this the wrong way?

 

[scottsen]

So... you have 2 tables. And it sound like you successfully created a relationship between them... yes?

Is one of the tables a proper "lookup" (dimension) table?

It's not super clear where/why you are using RELATED().

 

[TerryHogarth21]

So... you have 2 tables. And it sound like you successfully created a relationship between them... yes?

Is one of the tables a proper "lookup" (dimension) table?

It's not super clear where/why you are using RELATED().

I think it becomes a many to many relationship based on those 2 tables.

In SQL you would just do the joins and utilize NOT EXISTS, and Excel do vlookup, but what would be the equivalent in PowerPivot and is it possible?

The only reason I wanted to try utilizing PowerPivot is to do the calculations in there as it is supposedly faster than going through native Excel with lookups, then importing into PowerPivot Data source.

 

[scottsen]

I sorta suspect Power Query might be a better fit, but I'm way better with Power Pivot, so...

Okay, you won't be able to create a relationship (Many to many relationships aren't supported).

Maybe something like:
Table2Count := CALCULATE(COUNTROWS(Table2), FILTER(Table2, Table2[key] = MIN(Table1[Key]))
Table1Count := COUNTROWS(Table1)
Total Delta := SUMX(VALUES(Table1[Key]), ABS([Table1Count] - [Table2Count]))

There is probably easier ways, and you will wan to probably filter Total Delta to exclude 0's or whatever, but generally... those measures will work great, especially if you put Table1[Key] on rows of your pivot table.