Pivot table with dimensions from two different fact contexts

[Anonymous]

I have a concatenated fact table with two fact contexts and corresponding dimensions:

FactName	CargoID	CargoGroup	PathName	DateID	DamageTypeName
Transport	1	Package	A-->B	20180101
Transport	1	Package	B-->C	20180201
Transport	1	Package	C-->D	20180301
Transport	2	Envelope	A-->B	20180201
Transport	2	Envelope	B-->D	20180301
Damage	1	Package	A-->B	20180101	Chipped
Damage	1	Package	A-->B	20180101	Scratched
Damage	2	Envelope	A-->B	20180101	Crushed

I calculate the measure Count({<FactName = {'Transport'}, DamageTypeName =>} distinct CargoID) in a pivot table with dimensions DamageTypeName and CargoGroup. The result looks as follows:

Edit: Problem here is the fact context "Transport" does not have dimension values for DamageTypeName. Nevertheless I want calculate the total value for number of transported cargo to show for each DamageTypeName.

What I want it to look like is:

Attached is an example, which I used to test different behaviours.

Does anyone have an idea?

Kind regards,
Andy

[petter]

I believe this might be what you are looking for then:

You don't need the dimension CargoGroup in the Pivot Table.

Define three measures:

Expression: Count(TOTAL {<CargoGroup={*}>} distinct CargoID)         

Label: Gesamtwerte

Expression: Count({<FactName={'Damage'},CargoGroup={'Package'}>} DISTINCT CargoID)

Label: Package

Expression: Count({<FactName = {'Damage'},CargoGroup={'Envelope'}>} DISTINCT CargoID)

Label: Envelope

[petter]

or with a single measure and keeping the CargoGroup dimension:

Expression:

If( ColumnNo()=0,   // column 0 is the Totals column

  Count(TOTAL {<FactName = {'Damage'}>} DISTINCT CargoID)

,Count({<FactName = {'Damage'}>} DISTINCT CargoID)

)

[Anonymous]

Hello Petter,

thanks for your response. But I actually need the total measure from FactName = {'Transport'} to show for the dimension DamageTypeName. Problem here is, that the dimension has only values for fact context "Damage".

I edited the original post to clarify this.