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: