March 24, 2016 at 8:35 pm
I have a model with two fact tables. There's a one to many relationship between them. Because they're joined this relationship amplifies the 'one' side many times on the key.
I need to report on the distinct value from the 'one' side of the relationship, at the intersection of the key.
I have tried DISTINCT(column) and while the measure creates without error, when I load to excel and try to pivot I get error:
ERROR - CALCULATION ABORTED: Calculation error in measure 'FF Fact'[Measure2]: A table of multiple values was supplied where a single value was expected.
How do you do distinct in DAX against the column from the 'one' side, so that it deamplifies what is happening due to the join on the 'many' side?
--Quote me
March 29, 2016 at 11:25 am
"I have a model with two fact tables. There's a one to many relationship between them."
Begging your pardon, but shouldn't the two fact tables just share common dimensions? I don't think you really do want two fact tables directly related to one another, because they're effectively both on the "many" side of the relationship (with the dimension tables).
A graphical way of thinking about is like this:
[FactTable1]--(M,1)--[DimensionTable]--(1,M)--[FactTable2]
There's an example in Rob Collie and Avi Singh's book Power Pivot and Power BI... where they have Sales as Fact Table 1 and Support Calls in Fact Table 2. You can download it from their website to see what they're doing http://www.powerpivotpro.com -- the example you want is from Chapter 17.
Because the two fact tables have common dimensions, you can join on those and compare/do math on measures between the two tables. If you want a hardcore book on Star Schemas, you might try Christopher Adamson's book Star Schema: The Complete Reference. he talks a lot about having multiple fact tables sharing some dimensions.
March 29, 2016 at 10:34 pm
Because I was frazzled I thought it was understood that there is a dimension between them. Tabular SSAS wouldn't allow me to create a relationship between them even if I tried, actually. Thanks for the links and book suggestions, very much.
--Quote me
March 29, 2016 at 10:45 pm
How do you do distinct in DAX against the column from the 'one' side, so that it deamplifies what is happening due to the join on the 'many' side?
DISTINCTCOUNT(RELATEDTABLE('TableName')) ?
March 29, 2016 at 11:07 pm
The issue I had was more complex. Not only two fact tables with different levels of hierarchy which I reconciled with a composite key, in the manner suggested in one of these links (apparently oft used example is Budget - Daily transaction table):
http://www.daxpatterns.com/handling-different-granularities/
http://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/
Then I still had the following problem, and solution follows:
How to write a DAX formula that combines calculations involving columns from two FACT tables having different granularity, using a non-natural hierarchy, which is only partially represented by the attributes in the FACT table with lower granularity.
WHAT I WAS TRYING:
Here I'm trying to do calculations involving columns in the TCOST_Fact for lowest 3 levels of hierarchy and involving columns in PLAN_Fact table at highest 3 levels of hierarchy (POECDeliveryType, ProjectType, EngagementName) and it didn’t work.
Planned Rev1:=IF(ISFILTERED('TCOST_Fact'[ResourceName]), "NA", ?level 1
IF(ISFILTERED('TCOST_Fact'[CostType]),"NA", ?level 2
IF(ISFILTERED('TCOST_Fact'[ProjectName]), [PlannedRev_project], ?level 3
IF(ISFILTERED('TCOST_Fact'[POECDeliveryType]), [PlannedRev_POECDelType], ?level 4
IF(ISFILTERED('TCOST_Fact'[ProjectType]), SUM('PLAN_Fact'[TotalFixedFeePlannedRevenue]), ?level 5
IF(ISFILTERED('TCOST_Fact'[EngagementName]), SUM('PLAN_Fact'[TotalFixedFeePlannedRevenue]),0)))))) ?level 6
CORRECT:
The general concept as I understood it is that I need to “taxi” the where clause from TCOST_Fact over to PLAN_Fact side of the pivot table, and in the new context filter the PLAN_Fact table values (achieved by CALCULATE(FILTER(ALL(VALUES()))) function, and do a calculation if needed (SUM) or simply surface the PLAN_Fact row value to the report at the TCOST hierarchy.
On the highest level of the hierarchy, level 6, the edit is minor, but I do have to restate the CALCULATE(FILTER(ALL(VALUES()))) at each lower level of hierarchy in a compounded fashion. Even, at level 3, where I no longer need to do a calculation on columns involving another table. I even have to restate the CALCULATE(FILTER(ALL(VALUES()))) for the top 3 levels of hierarchy. And if the lowest two levels of hierarchy require some value or calculation, the highlighted has to be repeated as well.
Planned Rev:=IF(
ISFILTERED('TCOST_Fact'[ResourceName]), "NA", <--level 1
IF(ISFILTERED('TCOST_Fact'[CostType]),"NA", <--level 2
IF(ISFILTERED('TCOST_Fact'[ProjectName]), <--level 3
CALCULATE(
[PlannedRev_project],
FILTER(ALL('PLAN_Fact'[POECDeliveryType]), [POECDeliveryType] = VALUES('TCOST_Fact'[POECDeliveryType])),
FILTER(ALL('PLAN_Fact'[ProjectType]), [ProjectType] = VALUES('TCOST_Fact'[ProjectType])),
FILTER(ALL('PLAN_Fact'[EngagementName]), [EngagementName] = VALUES('TCOST_Fact'[EngagementName]))
),
IF(ISFILTERED('TCOST_Fact'[POECDeliveryType]), <--level 4
CALCULATE(
[PlannedRev_POECDelType],
FILTER(ALL('PLAN_Fact'[POECDeliveryType]), [POECDeliveryType] = VALUES('TCOST_Fact'[POECDeliveryType])),
FILTER(ALL('PLAN_Fact'[ProjectType]), [ProjectType] = VALUES('TCOST_Fact'[ProjectType])),
FILTER(ALL('PLAN_Fact'[EngagementName]), [EngagementName] = VALUES('TCOST_Fact'[EngagementName]))
),
IF(ISFILTERED('TCOST_Fact'[ProjectType]), <--level 5
CALCULATE(
SUM('PLAN_Fact'[TotalFixedFeePlannedRevenue]),
FILTER(ALL('PLAN_Fact'[ProjectType]), [ProjectType] = VALUES('TCOST_Fact'[ProjectType])),
FILTER(ALL('PLAN_Fact'[EngagementName]), [EngagementName] = VALUES('TCOST_Fact'[EngagementName]))
),
IF(ISFILTERED('TCOST_Fact'[EngagementName]), <--level 6
CALCULATE(
SUM('PLAN_Fact'[TotalFixedFeePlannedRevenue]),
FILTER(ALL('PLAN_Fact'[EngagementName]), [EngagementName] = VALUES('TCOST_Fact'[EngagementName]))
),
BLANK()))))))
<<><><I hope this helps someone<><><><>< DAX is so new to me and am getting pummelled.:w00t:
--Quote me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply