Friday, November 8, 2013

Tabular SSAS: get a value from a non-related table using DAX


Say you have the following schema:


You want your end users to be able to choose a CampaignName from the table on the left, and show corresponding sales for the customers that were included in that campaign from FactSales on the right.  Well that's easy using one of the many-to-many DAX calculation strategies such as:

Sales:=if(ISCROSSFILTERED(DimMarketing[CampaignName]),
CALCULATE(sum([ExtendedPrice]),FactCustomerTouchpoints),
,sum([ExtendedPrice]))

But now say you also like to see those sales either before or after the campaign started.  The MarketingDateKey on the left can help, but there is no relationship to the FactSales table we can use to associate the two - the only relationship available is through the CustomerKey which won't help us here.

But Wait!  LookUpValue DAX information function to the rescue!  We can use this function, in combination with the Filters function in order to get the proper row in DimMarketing, to retrieve the value for MarketingDateKey, and then use that value as a filter against the FactSales table, as seen below:

PostCampaignSales:=if(ISCROSSFILTERED(DimMarketing[CampaignName]),
CALCULATE(sum([ExtendedPrice]),FactCustomerTouchpoints,
filter(factsales,factsales[InvoiceDateKey] > LOOKUPVALUE(DimMarketing[MarketingDateKey],DimMarketing[CampaignName],filters(dimmarketing[campaignname]))) )
,sum([ExtendedPrice]))

Probably as clear as a freshly poured Guinness but if it helps at least one lost soul then cheers.