help with DAX lookup syntax

scabral79

New Member
Joined
Oct 15, 2017
Messages
3
hi,

i have 2 unrelated tables in an SSAS tabular model:

Location Values:
AcctId, LocationValue, Date
1 1,000,000 6-30-2017
1 500,000 6-30-2017
1 300,000 6-30-2017

Premium Values:
AcctId, PremiumValue, Date
1 100,000 6-30-2017

I want to bring the sum of the LocationValues into the PremiumValues table by joining on the AcctId and Date fields.

So in the Above example, i should end up with this in the PremiumValues table:

Premium Values:
AcctId, PremiumValue, Date, LocationValue
1 100,000 6-30-2017 1,800,000

I think LookupValue will work, but not sure of the syntax. I tried of couple of ways, but it keeps failing.

Scott
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I am guessing that there is a better way to do what you are trying to do. I am not saying that LookupValue wont work, just that this tends to be a function that Excel users gravitate to when starting with Power Pivot. What is your overall objective here?
 
Upvote 0
Hi Matt,

so what I need to do is get a total of the value for all locations that belong to an account. The location values exist in the Location Values table.

I need to bring that total location value for each account over to the Premium Values table for that same account and date.

Location Values table will have many rows for each Account and Date (given that each account can have many locations).

Premium Values only has 1 row for each account and Date (give that each account can only have 1 premium value on each date.

Date here is Month End Dates.

One other thing that I also need to do is that when I Total the Location Values for each Account and Date, I need to filter on "Active" locations only, which is a field in the Location Values table called Location Status.

thanks
scott
 
Upvote 0
So how about creating an account lookup table that contains a unique list of all accounts (no duplicates). Then join the other 2 tables to is new table using the account code. Place the account ID from the new table on rows in a pivot table, and the value from the location values
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top