topic Pivot table with dimensions from two different fact contexts in App Development

Pivot table with dimensions from two different fact contexts

Anonymous — Wed, 18 Apr 2018 14:20:59 GMT

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

Re: Pivot table with dimensions from two different fact contexts

petter — Wed, 18 Apr 2018 15:18:16 GMT

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

Re: Pivot table with dimensions from two different fact contexts

petter — Wed, 18 Apr 2018 15:32:38 GMT

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)

)

Re: Pivot table with dimensions from two different fact contexts

Anonymous — Thu, 19 Apr 2018 06:45:11 GMT

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.