Filter Pivot Columns

[CeeJay]

I got this table:

Department	ID	Section
Sales	1	W
Sales	2	W
Sales	3	W
HR	4	W
HR	5	W
Consulting	7	W
Consulting	8	W
Consulting	9	W
Sales	1	E
Sales	2	E
HR	6	E
Consulting	9	E
Sales	3	G
HR	4	G
HR	6	G
Consulting	7	G
Consulting	9	G
Sales	1	F
HR	4	F
HR	5	F
Consulting	8	F
Consulting	9	F

and would like to create a pivot table like this:

Count IDs		Section
Consulting	7			1	1
	8		1		1
	9	1	1	1	1
HR	4		1	1	1
	5		1		1
	6	1		1
Sales	1	1	1		1
	2	1			1
	3			1	1

but I would like to filter W > 0 and E < 1, like this:

Count IDs		Section
Department	ID	E	F	G	W
Consulting	7			1	1
	8		1		1
HR	4		1	1	1
	5		1		1
Sales	3			1	1

I tried filtering the column, I tried filtering the measure.

Then I tried GENERIC LOAD. But I still can't filter out E > 0

Please help me

[rubenmarin1]

Hi, you can try with an expression like: 

Count({<ID={"=Count({<Section={W}>}ID)>0"}>-<ID={"=Count({<Section={E}>}ID)>0"}>}ID)

[rafaelencinas · Accepted solution]

Hi !

You can create a new bookMark, 

1 - Create two Filters with W and E

2 - In the filter W type:  > 0 and type Enter

3 - In the Filter E type:  < 1 and type Enter

4 - Create an BookMark

Why: 

When you used expresions in filters It will work differently with selections

Bye !

[CeeJay]

Thank you very much, at the end I solved it by modifing the load statement.