October 9, 2014 at 4:40 pm
Hello All, I am trying to migrate one of our cubes from SSAS 2008 to 2012 Tabular and am struggling to figure out how to port over a specific calculated column from MDX to DAX. I am attempting to use two different measures from two different measure groups( Install_001 from FactUsers and Cost from FactSpending) and basing a calculation off of that.
I have two measure groups FactUsers and FactSpending
--The logical primary key of this table is a combination of (CampaignCode, CampaignDateId)
CREATE TABLE #FactSpending(
CampaignCode varchar(255),
CampaignCodePK INT,
CampaignDateId INT,
Views INT,
Clicks INT,
Cost INT,
Engagement INT)
--The logical primary key of this table is UserId.
CREATE TABLE #FactUsers(
UserId INT,
AllocatedCost INT,
Views INT,
Clicks INT,
AllocatedHouseholdCount INT,
Install_001 INT,
DScore INT,
CampaignCodePK INT)
This is the calculated column in MDX which works fine in SSAS 2008 :
IIF([Measures].[User Install_001]=0, NULL, [Measures].[TotalCost] / [Measures].[User Install_001])
And this is what I am attempting to write in 2012 Tabular in DAX :
Campaign CPI_001:=IF([FactUsers[Install_001]]=0,BLANK(), FactSpending[Cost]/Users[Install_001])
I get a "Semantic error: The value for column 'Install_001' in table 'Users' cannot be determined in the current context. Check that all the columns referenced in the calculation expression exist, and that there are not circular dependencies ... The column does not have a single value; it has many values, one for each row of the tables, and no row has been specified"
I read up on some blogs and tried to write something like this -
CPI_001 :=
CALCULATE (
SUM ( FactSpending[Cost] ),
USERELATIONSHIP (
FactSpending[CampaignCodePK],
FactUsers[CampaignCodePK]
)
)
I end up getting this - Measure 'FactSpending'[CPI_001] : USERELATIONSHIP function can only use the two columns references participating in relationship.
October 10, 2014 at 12:43 am
I guess you are trying to use CampaignCode as a type of relationship between the two fact table.
However I guess this is not a true PK-FK relationship, meaning this is not a 1-N relationship but rather a M-N relationship right. These are not supported out-of-the-box within SSAS Tabular. You need to define a relationship that binds one row of fact table Spending directly to one row of fact table Users.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 11:49 am
Thank you for taking to reply!
Yes there is not a 1:1 relationship between the two fact tables. But, is there a work around for many to many relationship between tables? I would bet there is but rather complicated?
Thanks again.
October 11, 2014 at 1:48 am
gasblr (10/10/2014)
Thank you for taking to reply!Yes there is not a 1:1 relationship between the two fact tables. But, is there a work around for many to many relationship between tables? I would bet there is but rather complicated?
Thanks again.
Unfortunately it is rather complicated. Take a look here[/url].
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy