Any idea on processing lists, that have variabe number of items for filtering or grouping.

[Anonymous]

Hi all,

I have a List with a variable number of items (8-9 items, where each item is a word or short phrase) in a dimention:

A , B

A

B, C

A, C

I would like to be able to Group:

A	3
B	2
C	2

or Filter:

A	Dimention Row 1 Details & associated Fact rows
B	Dimention Row 1 Details & associated Fact rows
A	Dimention Row 2 Details & associated Fact rows

I have tried a couple of methods...
The best Idea I could think of was, was to use a checksum like fuction to create an index & snowflake the model design. But I couldn't find a string to number function that would achieve this easily.

The only idea i have left,
is to use the list.contains and creat a bunch of new columns, but that hard codes the items and adds lots of empty fields.

Any Ideas would be great?

p.s. sorry i originally had a lot more context, particulary all the ideas I tried but PBI lost the content :(.

----

Notes: I realise some of what I wrote above may have been a bit confusing. I was after the abillity to visually slice and dice by filtering to create groups etc. The asnwer provided below does do this well enough, it enables filtering of the dimenion and the count per item.   I could not get it to also filter the fact table but I'm ok with that for this current need.

[smpa01 · Accepted solution]

@Anonymous  you can write a single DAX measure

Measure =
VAR _0 =
    ADDCOLUMNS ( dim, "new", SUBSTITUTE ( dim[entries], ",", "|" ) )
VAR _1 =
    GENERATE (
        _0,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
            "seperateValue", PATHITEM ( [new], [value], TEXT )
        )
    )
VAR _2 =
    ADDCOLUMNS (
        'Table',
        "@count",
            COUNTX (
                FILTER ( _1, [seperateValue] = EARLIER ( 'Table'[Column1] ) ),
                [seperateValue]
            )
    )
RETURN
    SUMX ( _2, [@count] )

[Anonymous]

@smpa01this works both for grouping as you show and for filtering rows in the dimention.

To enable the filtering on the rows, simply add the measure to the table/visual with the a "filter on this visual" where "Measure is not blank".  Although this only seems to work with data from the dimenion table.

For others trying this, the code won't work if null is an options, so you wil have to replace them with a meaful text value.

[v-yanjiang-msft]

Hi @Anonymous 

Here's my solution.

Method1

1, Split column by delimiter.

2, Copy column1.2 to a new table.

3, Append column1.1 and the copied new column.

After append, it shows like bellow:

<![if !vml]>

<![endif]>

4, Do not select “null” value.

<![if !vml]>

<![endif]>

5, Use group by count function to column1.1

<![if !vml]>

<![endif]>

Then it becomes like this:

<![if !vml]>

<![endif]>

Method2

1, Split column by delimiter.

<![if !vml]>

<![endif]>

2.Create a new table

<![if !vml]>

<![endif]>

3.Create a new measure for the result.

<![if !vml]>

<![endif]>

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , In power query Split by delimiters  and then group with count

Split Column Power Query: https://youtu.be/FyO9Vmhcfag

group by

https://docs.microsoft.com/en-us/power-query/group-by