topic Re: Columns in Custom Visuals Development Discussion

Columns

LF — Sun, 20 Nov 2022 23:43:06 GMT

Beginners Power BI user. I have a file that was created in MS Lists that allow the users to select multiple options. For example I have a column with four brands is selected as a a single value and with others. I would like to measure the total use of each brand, together or separate How to I sort or separate this column. Not sure if I'm articulating the question correctly. Do I have to create and measure, dax special column, or can it be sorted in a advance search For example

( Coke;

coke, Pepsi;

coke, Pepsi, sprite;

Coke;

Pepsi, sprite;

Coke, root beer) I would like to create a chart that tells me how many time coke was selected overall.

Re: Columns

lbendlin — Tue, 22 Nov 2022 23:04:23 GMT

Assuming your category list is constant (you can use Power Query to create that list if not) you can then use

CONTAINSSTRING function (DAX) - DAX | Microsoft Learn

to see how many times each category is present in your data rows.

see attached

Re: Columns

LF — Sat, 26 Nov 2022 14:34:55 GMT

Thank you!

Re: Columns

LF — Mon, 20 Feb 2023 17:34:38 GMT

Sorry for the delay, I have attempted to try the methods suggested with no luck. This is a more accurate interruption of the data I am working with. It is from MS list and the users have the ability to select multiple options in certain columns. I would like to measure the data to understand how many times each value is mentioned. How can I clean and create a list that I can measure. Also the data is going to continue to change and grow over time. Thanks for your assistance.

Re: Columns

lbendlin — Mon, 20 Feb 2023 18:02:42 GMT

please provide the sample data in usable format, not as a screenshot or picture.

Please show the expected outcome based on the sample data you provided.

Re: Columns

LF — Mon, 20 Feb 2023 22:10:40 GMT

Sample data (List).pbix

Hi,

Thanks for your help. I would like to see how many time each value is selected. Should I be creating a measure? and if so how can I complete this. Or should I create a column when I am cleaning the data. Or is it a Dax that would allow me to filter without duplicates. I am only able to filter by row. The system thinks each row is a unique value and I would just like to find the total number as my dataset is going to contiue to grow. Please let me know if I can add anything else. Thank you

Re: Columns

lbendlin — Mon, 20 Feb 2023 22:34:20 GMT

No need for any DAX. Some small Power Query transforms do it. See attached.

Re: Columns

LF — Mon, 20 Feb 2023 23:47:14 GMT

Thank you! Yes this is what I wanted to do. Will new entries automaticly be added to the list. Also how do merge the relationship. Should it be many to many?

Re: Columns

lbendlin — Tue, 21 Feb 2023 01:32:44 GMT

new entries will automatically be added.

What relationship? It's a single table.

Generally you want to avoid many-to-many relationships. They are most often a sign for a broken data model.

Re: Columns

LF — Tue, 21 Feb 2023 01:58:29 GMT

Ah makes sense. The reason I ask is because I completed the steps you provided as a duplicate in the panel as I would also like to still have the data as selected from list. Is there a way to have all new data sorted like this and raw as selected. Could I create a column or measure. Is there a way to have the best of both worlds. (List data and grouped)

Re: Columns

lbendlin — Tue, 21 Feb 2023 02:00:37 GMT

I'm not clear what the requirement is. Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Columns

LF — Tue, 21 Feb 2023 22:42:51 GMT

I apprecate the support. Thank you