Power BI : Managing lists in columns

Question

I'll start by saying that I'm fairly new to Power BI and am finding my way around, but really am struggling with the concept of lists in a column. I'll explain where I have got in a made up but real world example - sorry if it is rather long winded.

So for my example I have orders in a restaurant, as per the following table:

ID Person Order                      Alcohol
1  Bob    Pizza,fries,beer           Yes
2  Fred   Pizza,salad,dressing,wine  Yes
3  Pete   Lasagne,fries,cola         No
4  Dave   Pizza,fries,cola           No
5  Bob    Sundae                     No

I would like to be able to show on a report

A data card showing the count of orders
A list of all of the orders showing all the columns as they are above
A bar chart showing the number of times each item was ordered
A doughnut showing orders which included alcohol

I'd also like to be able to filter across those visualisations, i.e. clicking a row in a bar chart or a portion of the doughnut filters the other visualisations. So, I could for example see which orders included alcohol, or how many people ordered fries and alcohol.

This is where I am becoming stuck.

In order to get the count of the each of the items ordered, I have duplicated the Order column, calling it OrderItem, and then split OrderItem on the comma delimiter, creating new rows. Doing this gives 14 rows, but allows me to

Show the data card (counting distinct ID)
Show the list of orders (Table visualisation showing the columns other than OrderItem)
Show a bar chart of all of the items and the number of times they were ordered
Show a doughnut of alcohol (BUT it is showing 14 items because it is counting rows in the table rather than the overall orders)
Do the filtering across visuals

So I'd like to know how I can get the count of alcohol to reflect the distinct orders rather than the total number of rows.

One approach I tried was to create a duplicate table in PowerQuery, and split that duplicate into rows for each Order Item. That works to a fashion in that I can use the original table for the alcohol doughnut and the table with a row for each item for the counts.

The downside with that approach is that if I click the "fries" row in the bar chart, it doesn't then filter the alcohol doughnut to show which orders with fries had alcohol.

Any advice on how to get the correct count in the doughut AND the filtering would be very much appreciated!

Are you using any measure for the Donut chart? If yes, please show the code for that measure. — mkRabbani
No, I wasn't using code for the measure - although will now having seen the answer from xX_Blue! — Martyn

xX_Blue xX_Blue · Accepted Answer · 2020-10-22T13:35:42

Separate order by Comma (Already completed)
Unpivot the data. First select the relevant order columns, then click unpivot columns at top.

Now each order has a value to it, so you can begin to specifically start counting each specific order item. These values will be unique.

As you mentioned, the issue now is alcohol, ID, and person are duplicated. This is where measures are important. So for alcohol, you create a measure.

Measure = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Alcohol] = "Yes"))

To get the no values, simply do the same measure with "No"

Power BI : Managing lists in columns

1 Answers