Dynamic column value based on slicer selection

[Anonymous]

Hi,

Have searched the previous posts on Dynamic selected, but cannot find solution to my scenario.

Below are the tables I have.

Table: Material Type
Mat Type Key	Mat Type Text
A	AAA
B	BBB
C	CCC

Table: Material
Material Key	Material Text	Mat Type	Hier L1	Hier L2	Hier L3	Material Group
M1		A	H11	H21	H31	MG1
M2		A	H12	H22	H32	MG3
M3		B	H13	H23	H33	MG3
M4		C	H14	H24	H34	MG4
M5		C	H14	H25	H25	MG4

Fact table: Stock
Material Key	Stock
M1	100
M2	200
M3	300
M4	400
M5	500

In the datamodel, below are the relationships:

Stock.MaterialKey = Material.MaterialKey (many to 1)

Material.Mat Type = Mat Type.Mat Type Key (many to 1)

In the report, I have slicer on Mat Type.Mat Type Key. Based on what is selected, I need to show Stock either by Hier L1 or Hier L2 or Material Group.

Slicer on Mat Type
A B C


When A is selected, I need to show Stock by Hier L1
Type Stock
H11 100
H12 200

When B is selected, I need to show Stock by Hier L2
Type Stock
H23 300


When C is selected, I need to show Stock by Material Group
Type Stock
MG4 900

Please help how to achieve this.

Regards.

[v-lili6-msft]

HI  @Anonymous 

When A is selected, I need to show Stock by Hier L1
Type Stock
H11 100
H12 200

Why it shows Hier L1 but not Hier L2 or Hier L3 or Material Group?

When B is selected, I need to show Stock by Hier L2
Type Stock
H23 300

Why it shows Hier L2 but not Hier L1 or Hier L3 or Material Group?

When C is selected, I need to show Stock by Material Group
Type Stock
MG4 900

Why you need it show Material Group but not Hier L1 or Hier L2 or Hier L3?

What is the logic/rules for it?

Regards,

Lin

[parry2k]

@Anonymous great explanation but it is not clear by selecting mat key why different Hier will show up, like in this case, what is the logic to show stock by Hier L2?

When B is selected, I need to show Stock by Hier L2
Type Stock
H23 300

[Anonymous]

Thanks @parry2k. That is my business requirement. The report should show different column values based on what is selected. Are you able to help how do I achieve this?

When B is selected, I need to show Stock by Hier L2
Type Stock
H23 300 

[parry2k]

@Anonymous ok, so you will never see stock for mat type B by any other Hier. Are these fixed business rule or it is dynamic?

[Anonymous]

Thanks for the reply @parry2k . 

Correct these are fixed rules.