DAX Filter unrelated tables

[rpmitchell]

Hello. I am trying to run a calculation that uses only certain rows from an unrelated table. Here is the scenario:
I have two tables with account numbers. Each account number is repeated multiple times in each table. The account value is different in each table. I built a pivot off Table1. I want a sum of the values in table 2, for all accounts in table 1, that happen to be a part of the pivot table filter context. The actual account number isn't on the pivot, just attributes of the account. I've tried:

Calculate([Sum(Tbl2[Value],
Filter(Tbl2,Tbl2[Account] = Tbl1[Account]))

but it doesn't work. It can't determine the value of the Tbl1 account in the current context, blah, blah, blah...
Any ideas on how to fix this formula?

 

[Matt Allington]

Both these tables are data tables, so they have to be on the many side of any relationship. Why not create a new lookup table that contains a superset off all account numbers. Then join both of your current tables to this new table, then put the account number from the lookup table in your pivot. You can then create 2 simple calcs

val1=sum(table1[value])
val2 =sum(table2[value])

 

[rpmitchell]

Both these tables are data tables, so they have to be on the many side of any relationship. Why not create a new lookup table that contains a superset off all account numbers. Then join both of your current tables to this new table, then put the account number from the lookup table in your pivot. You can then create 2 simple calcs

val1=sum(table1[value])
val2 =sum(table2[value])

Thanks Matt. The problem with this is that I have close to a million account numbers, so I don't want them on the pivot. I want an attribute of the account, for example "Account Age", "Patient Type", "Payor, etc". These attributes may or may not match in the two tables. I've thought about a bridge table, but I think the maintenance on it would be massive. I'm leaning towards a Calculated column in table 1 with a Lookupvalue(). I could match the required attributes of each account and return the value I need. I just wonder how it would affect my model performance. Is it ok to run that kind of calc column with a million rows?

 

[Matt Allington]

If the attributes don't match, how can you put them in a pivot table and aggregate data from 2 tables? If they don't match, what is the point of a calculated column? So just add the attributes to the lookup table and add the attributes to the pivot.

 

[philiplaucpa]

why don't you use the Table2 to build a slicer with the Table2's attribute. This will save you a lot of trouble.

If you have a combo set, why not put the parameter on Table 1 and just display the slicer with the key. When user select the key, the set of parameter will be returned.

For your error message, you need to put a dummy formula to wrap around Tbl1[Account]. Maybe you can try FirstNonBlank().