I have a table that contains a list of words and I would like to know how I can use DAX to do the following example:
| ID | Items |
|---|---|
| 1 | Apple |
| 2 | Banana |
| 3 | Apple, Banana |
| 4 | Orange, Apple |
| Items | Total |
|---|---|
| Apple | 3 |
| Banana | 2 |
| Orange | 1 |
The issue is some of the rows have more than one item and using Count only takes the first item on the list.
Output I don't want:
| Items | Total |
|---|---|
| Apple | 2 |
| Banana | 1 |
| Orange | 1 |
The current process I did was split the columns, use pivot, then do count.
You can create a separate table based on your source table where you will always have your item wise count. Use this below Advanced Query code for your new table.
Just change the source table name in the code with your original table name.
let
Source = your_table_name,
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Items", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Items"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Items", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Items", Text.Trim, type text}}),
#"Grouped Rows" = Table.Group(#"Trimmed Text", {"Items"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
Here is the final output-
