Slicer: Show single values from multiple selection attributes

PJG
PJG Member
in Charting

In our database, we have a multiple selection attribute called Site. In our Dataset, it's shown in a column like these examples with a pipe symbol being the delimiter:

Project 1: Rome | London

Project 2: London | Paris | New York | Melbourne

Project 3: Paris | Melbourne

If I select this attribute as a slicer, it shows me every combination that exists in this attribute, i.e. exactly as they're listed above. I don't want that - I want it to show only the individual sites. Then, for example, if someone selects London, Project 1 & 2 would be shown.

Is this possible?

Answers

  • ColemenWilson
    ColemenWilson Coach

    Yes. You can pivot out the data so that every city is it's own row of data. You can do this in Magic ETL using the Split Column and Dynamic Unpivot tiles.
    1. Split the values into new columns using the Split Column tile. Be sure to choose "|" as the delimiter.
    2. In the Dynamic Unpivot tile, choose your Project # and Project values as columns not to be pivoted.
    3. Use the Filter Rows tile to remove any null values for the pivoted values
    4. Drop the 'Names' column from the Dynamic Unpivot
    5. Final result looks like this:

    Screenshot 2025-03-24 at 9.54.43 AM.png

    6. When building cards, you will have to construct them such that values aren't duplicated. I don't know how you are trying to visualize this data, but this gets you to that step.

    If I solved your problem, please select "yes" above

  • PJG
    PJG Member

    Hi Colemen - Thanks for this reply a couple of weeks ago. Finally have some time to come back to it today.

    I think I had your detailed steps correctly implemented, but my results were making no sense to me, and I spent a while troubleshooting. Ultimately, it seemed that Dynamic Pivot and "|" as the delimiter were incompatible. To me, it seemed that instead of the "|" separating values within the Site column, it was actually separating the columns. Please let me know if you think it's some other issue, but even though I was only selecting Site, I was seeing values from multiple other columns in my new column.

    Anyhow, I think I have solved it by using Unpivot instead of Dynamic Unpivot, which does not seem to have the same problem when using "|" as the delimiter.

    image.png

    Now I am realizing I have another problem which I should have considered from the start. This filter will have no impact unless all other cards on the dashboard are using the same dataset as my ETL output one. I don't suppose there's a way around this unless I switch their datasets to be the same?

  • Manasi_Panov
    Manasi_Panov Contributor

    Hello @PJG,

    I can confirm that it also works with Dynamic Unpivot as well and the result is exactly what you need.

    The filter may apply to other cards using different datasets, provided they share a column with identical name and values. For example, if both datasets contain a 'City' column with the same name and values, you can apply a City filter across cards built on different datasets simultaneously.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories