Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using below sample data
1) I want to show Module as drop down like below
2) I want to show ID count based on selected module like below
example If I select Module1 and Module2 then Count should show 1
Drop Down:
Sample Data:
When both Module1 and Module2 are selected : it should show ID count as 1
For the dropdown you can use a normal filterbox and set its height to only one cell high. When you click it, the list with all values will be shown:
For the label I used
=Concat(distinct Module, ', ')
but you can get creative here.
For the ID count there are two options, depending on how you want no selections in the field Module to be treated:
If no selections should be treated as if all values were selected, use this one:
Sum(Aggr(If(Count(Module) = GetPossibleCount(Module), 1), ID))
If no selections means no Module should be considered, thus the count should be 0, use this:
Sum(Aggr(If(SubStringCount(GetFieldSelections(Module), Module) > 0, 1), ID))
Here is the data I used:
Modules:
NoConcatenate Load * Inline [
Module,ID
Module1,2
Module1,3
Module2,1
Module2,3
Module2,4
Module3,2
Module3,3
];
Use Crosstable() to Unpivot the data
CrossTable(Module,Values,1)
Load
ID,Modul1,Module2,Module3
From XyzSource;
Then use the Module field as filter field measure as count(Distinct ID)