Filter rows in a Pivot table

daria — Fri, 04 Dec 2020 16:50:09 GMT

Hi!

I am stuck with a task. Please, help!

I have the next data:

SourceData: Load * inline [SetName, Date, Type, Value, color 'A', 30/10/2020, Type_1, 1, red 'A', 26/10/2020, Type_1, 2, red 'B', 30/10/2020, Type_1, 3, green 'B', 26/10/2020, Type_1, , green 'C', 30/10/2020, Type_1, 2, red 'C', 25/10/2020, Type_1, 3, red 'D', 25/10/2020, Type_1, 4, green 'A', 30/10/2020, Type_2, 4, red 'A', 26/10/2020, Type_2, 3, red 'B', 30/10/2020, Type_2, , green 'B', 26/10/2020, Type_2, , green 'C', 30/10/2020, Type_2, 1, red 'C', 25/10/2020, Type_2, 5, red 'D', 25/10/2020, Type_2, 2, green 'E', 25/10/2020, Type_2, , green 'E', 26/10/2020, Type_2, , green 'E', 25/10/2020, Type_1, , green 'A' , , Type_3, , black 'B' , , Type_3, , black 'C' , , Type_3, , black ];

I have a Pivot Table where I calculate Count of Values. And filter by Color.

I want to display all possible types always, but SetName should be filtered by Color.

For example, when I set Color = "Black" I want to see the next result:

I use two expressions:

1) Count( Value ) + 0 * Count( {1-1<color={"$(=(color))"}>} 0). Types display correctly, but I see ALL values of SetName

2) Count(Value). SetNames dysplay correctly, but Types are missing (See screenshot below)

I marked the result what I expect in Yellow.

Do you have any ideas on How to solve this? Any help is appreciated!

Re: Filter rows in a Pivot table

GaryGiles — Sat, 05 Dec 2020 13:11:44 GMT

For your measure expression, use: Count(Value)+Sum({1} 0)

For your SetName dimension expression, use: =Aggr(maxstring(SetName), SetName)

Then, when you filter on color, it should give you the desired results.

Re: Filter rows in a Pivot table

daria — Mon, 07 Dec 2020 07:46:45 GMT

It works! Thank you very much!

topic Filter rows in a Pivot table in App Development

Filter rows in a Pivot table

Re: Filter rows in a Pivot table

Re: Filter rows in a Pivot table