Counting unique values in a multi value cell

[Chris203]

Hi there,

I built a small status report using MS List and am by no means a programmer or math-wiz. This is just to help our small team better report on work done.

I need urgent assistance with the reporting in PowerBI desktop.

I get my data from MS List. A part of the data is multiple selections in one cell.

i.e.

	Selected items
Sale 10	B; C; D; E; A
Sale 12	A; C; D
Sale 13	D; A; B; E
Sale 14	A;
Sale 15	C; D; A; E
Sale 16	A; C; E
Sale 17	D ; A; C;

I need a clustered column-chart in PowerBI that shows number of sales per item

A=7; B=2; C=5; D=5; E=4

Currently I can only get PowerBI to show the following.

Obviously, there is more to my list and these couple of these fields that needs the same treatment.

Note: there is also amounts in the rows and by using “Unpivot” these get duplicated, and my totals then stop being true.

[Washivale]

Hey @Chris203 ,
you might need a list of unique items that goes to the concatenated column to get what you are looking for

probably something like below
List Items
A
B

C
D

then you will write a measure 

Counts = var __list_item = MAX(List_Items[Values])

return

CALCULATE(COUNT('Table'[Selected Items]), CONTAINSSTRING('Table'[Selected Items],__list_item))

then you will use List_Items[Values] and newly created measure on your clustered chart



thanks,
Sandeep