Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
It works! Thank you very much!