topic Measure to count columns that have the same strings of text in a different order in DAX Commands and Tips

Measure to count columns that have the same strings of text in a different order

cjbaguley — Tue, 30 Apr 2024 06:12:26 GMT

Hey Folks,

I have a column of data that contains a topic or multiple topics that are seperated by ";" that may be in a different order.

I'm trying to count the times particular combinations of topics are put together, i.e.

As appears in regular table count

Topic 1;Topic 2;Topic 3 - 1

Topic 3;Topic 1;Topic 2 - 1

Topic 4 - 1

Output I'm trying to get to:

Topic 1;Topic 2;Topic 3 - 2

Topic 4 - 1

I have found the following solution on Stack Overflow that seems to be exactly what I'm talking about, however the context is for other databases than PowerBI / Power Query.

r - Group strings that have the same words but in a different order - Stack Overflow

I'm hoping that this is something that can be acheived via a measure rather than within power query.

I already have a bridge table that lists all individual topics, so I'm wondering if its something I might be able to leverage as part of the meaure, but I'm very lost on this presently.

Thank you for your help.

Re: Measure to count columns that have the same strings of text in a different order

MattAllington — Tue, 30 Apr 2024 06:18:09 GMT

The general approach is to reshape your data using Power Query before loading. Exactly how you do that really depends on your data. But in simplistic terms, if you just split the column based on delimiter selecting "to rows" as an advanced option, you end up with one row per item. Then you have a column you can count. Your source table now has more rows, of course. What to do about this really depends on your data and use cases.

Re: Measure to count columns that have the same strings of text in a different order

tamerj1 — Wed, 01 May 2024 10:43:49 GMT

Hi cjbaguley
In order to aggregate by a Column you have to the have the column physically exists in the data model either as a column in the same table or as a column in a separate table. Unless you want to present the result in card visual as a single string with different lines delimited with UNICHAR ( 10 ). That could be practical only if the result contains few number of lines which I don't think is the case, is it?