Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LF
Frequent Visitor

Columns

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. 

 

 

 

3 ACCEPTED SOLUTIONS

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

 

View solution in original post

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.

View solution in original post

LF
Frequent Visitor

I apprecate the support. Thank you 

View solution in original post

11 REPLIES 11
LF
Frequent Visitor

I apprecate the support. Thank you 

LF
Frequent Visitor

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) 

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.

LF
Frequent Visitor

Thank you!

lbendlin
Super User
Super User

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.

lbendlin_0-1669158226665.png

 

see attached

 

 

LF
Frequent Visitor

16769141389192226826822575416835.jpg

 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. 

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.

LF
Frequent Visitor

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  

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

 

LF
Frequent Visitor

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? 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors