I'm currently working on a dashboard. In the data model there is a dimension called "Category" with 4 values:
I'm needing to build a pivot chart that in a single column or dimension shows the possible combinations of the same field "Category" and in the expression count the number of users that have granted permision in that combination. The expected result would be something like this:
Count (Distinct .........USERNAME)
Category 1 and Category 2
Category 2 and Category 3
Category 2 and Category 4
..And so on
For example, the following expression returns the correct calculation to count the users that have the combination of category "Category 1" and "Category 2", but it is not functional for me since it does not have it in one dimension.
In the example of the qv file I made the attempt with a bar chart that has no dimensions, it is not what I need. I think the calculation can be made simpler, maybe from the same script I can create those combinations in a new dimension but I don't know how.
Something like this I imagine to have the category combos as dimension field. However, the calculation it performs on the pivot chart is not correct. For example, in your example UserID 1 has only the value "Category1" in the CategoryName field, so that user should not appear in the calculation of the combinations and it is because it only has a single category, it does not have more than one category to be able to arm the category combos. And so for other users. Can the pivot chart expression count when a user has the combination of categories?
The data was from a dummy example, but in reality it can be many more categories. In the example I tried it with a listbox as a filter, which helps but I lose the option to see it in a summary. To be able to visualize it from a pivot chart that shows as a dimension all the combinations of categories and in the expression that counts the total number of users that have that combination is what I need.
I tested it with a bar chart and at least in the example there is an expression for each combination of categories. That bar chart has no dimensions and I lose interaction. I would also like to be able to order from highest to lowest the combination that has the highest number of users and I am not succeeding.
Hi @fosuzuki Thank you very much, you have helped me a lot and it's a great solution. I did the test with the real data and the pivot chart works as I wanted.
One last question, I would also like the listbox with the "CatCombo" field to be available on the dashboard, and when including it I have noticed that it brings some combos that users do not have, for example username 17 which has the values "Category 2" and "Category 4" and the listbox shows the combination "Category 1 and Category 2" and so on.. that username17 doesn't really have. Is there a way for the listbox to show "Category 4 and Category 2" as a related value? something like that: