topic Re: DAX- To convert a comma delimited string column into a list and filter the Table in DAX Commands and Tips

DAX- To convert a comma delimited string column into a list and filter the Table

suman1985 — Wed, 06 Sep 2023 18:47:45 GMT

Hi All,

I am newbie to DAX. Please help me with your suggestions.

I have a scenario where I have a Input Table with Comma delimited string (Names) and sales Column. Please see Input Table below.

Input Table
S.No	Names	Amount
1	a,b,c	100
2	b,c	150
3	a,c	200
4	b,d	150
5	a,c,d	50
6	d	250
7	c,d	300

I wanted to show a output table as Distinct Names and sum of Amount

EG: name a is at rows 1,3,5 -so , sum(amount) where ever the a exists which is 350.

Now, I wanted to convert this table data to a Result Table as shown below.

Result table
Distinct Names	Amount
a	350
b	400
c	800
d	750

Please create this output table using only DAX.

Appreciate the help.

Thanks

Suman

Re: DAX- To convert a comma delimited string column into a list and filter the Table

AnthonyGenovese — Wed, 06 Sep 2023 18:56:27 GMT

The way I would do it - I would first need a table with all your distinct names. I would do that step in Power Query. once you have your table of distinct names, the measure to create your amount measure is pretty straight forward.

You will do something like
calculate(sum(amount), Containstring('InputTable'[Names],selectedvalue('NewPowerQueryTable'[Names])

https://dax.guide/containsstring/

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

Re: DAX- To convert a comma delimited string column into a list and filter the Table

suman1985 — Thu, 07 Sep 2023 12:13:06 GMT

Hi AnthonyGenovese

Thanks for the suggestion.

Is there a way we can write DAX with not to have a Slicer for the Distinct Names.? I wanted everything to be wrapped within the single DAX with all the totals summed up by the Names.

Thanks

Re: DAX- To convert a comma delimited string column into a list and filter the Table

eliasayyy — Thu, 07 Sep 2023 12:20:46 GMT

hello suman1985 if you are able to use powerquery , right click on the column with commas and choose split by delmiter ,

press on advanced and select by rows

result

now in dax just make a simple sum('Table'[Amount])

Re: DAX- To convert a comma delimited string column into a list and filter the Table

suman1985 — Thu, 07 Sep 2023 12:23:02 GMT

Hi AnthonyGenovese

Thanks for quick response.

Unfortunately, I cant use the Power Query. Can this be done only in the DAX.

Appreciate the help..

Re: DAX- To convert a comma delimited string column into a list and filter the Table

eliasayyy — Thu, 07 Sep 2023 12:34:14 GMT

oh i see in that case lets create a new calculated table suman1985

NamesSplit = VAR MaxNames = CALCULATE(MAXX('Table', LEN('Table'[Names]) - LEN(SUBSTITUTE('Table'[Names], ",", "")) + 1)) RETURN FILTER( GENERATE( 'Table', ADDCOLUMNS( GENERATESERIES(1, MaxNames, 1), "SingleName", TRIM(PATHITEM(SUBSTITUTE('Table'[Names], ",", "|"), [Value])) ) ), NOT(ISBLANK([SingleName])) )

RESULT

if i help you please accept as solution and leave a kudos

Re: DAX- To convert a comma delimited string column into a list and filter the Table

suman1985 — Thu, 07 Sep 2023 15:13:58 GMT

Hi eliasayyy

Thanks for the suggestion .

Unfortunately, I dont want to use Power Query for this scenario. I would rather need this table output within a single DAX .

Thanks

Re: DAX- To convert a comma delimited string column into a list and filter the Table

suman1985 — Thu, 07 Sep 2023 15:56:17 GMT

Hi eliasayyy

Awesome , it worked.😊.You saved my time..

I have added two more scenarios for this. Please be patient to solve this. Appreciate the help.

Please modify the DAX based on the modified input data.

I have added a group column as shown below input table

1. Now, I have a group slicer coming from a table INPUT Table and i see now there is no relation exists and wanted to filter this new DAX table with the group slicer with multiple values selected. Output should be same as shown above.

2. And I want to add dynamic ranking based on the slicer selection.

INPUT Table

Group	Tasks	Amount
Group1	a,b,c	100
Group2	b,c	150
Group3	a,c	200
Group4	b,d	130
Group5	a,c,d	50
Group6	d	250
Group7	c,d	300
Group1	a,d	100

Result table
Distinct Names	Amount	Rank
a	450	3
b	380	4
c	800	2
d	830	1

Thanks

Re: DAX- To convert a comma delimited string column into a list and filter the Table

eliasayyy — Fri, 08 Sep 2023 06:30:10 GMT

can you show me what slicer you are using please

Re: DAX- To convert a comma delimited string column into a list and filter the Table

eliasayyy — Fri, 08 Sep 2023 06:36:27 GMT

Ranking =

RANKX(ALLSELECTED(TasksSplit[SingleName]),[Total Amount],,DESC,Dense)

if its solved your issue kindly accept it as solution

Re: DAX- To convert a comma delimited string column into a list and filter the Table

suman1985 — Fri, 08 Sep 2023 06:45:57 GMT

Hi eliasayyy

I am using group Slicer from the Input table. Please see below screenshot.

Earlier input table has only Tasks and amount. I have added Group column now and want to filter the result table with Group Column and rank the table accordingly.

Thanks

Re: DAX- To convert a comma delimited string column into a list and filter the Table

suman1985 — Fri, 08 Sep 2023 06:53:48 GMT

Hi eliasayyy

Ranking works fine. But can you take the group column as slicer from the input table instead of from the Derived calcualted table.

Thanks

Re: DAX- To convert a comma delimited string column into a list and filter the Table

eliasayyy — Fri, 08 Sep 2023 07:24:10 GMT

yes just create a relationship between group and group or even better create a dim table wjich is always a great practice that returns unique values of group and then create a relationship between the two

DimGroup = ALLNOBLANKROW('Table'[Group])

Re: DAX- To convert a comma delimited string column into a list and filter the Table

suman1985 — Fri, 08 Sep 2023 07:36:28 GMT

Thanks for the suggestion eliasayyy

Actually, this is one of the visual logic we are implementing on a report which has 7 visuals.

There are 6 slicers and Group slicer is one among them used on the report. All these 6 slicers are begin taken from the input Data.

So , I cant create any new dimensions for the Groups as they are already being pulled from the Input data

The requirement I shared is specifically for this visual.

Please include the group slicer from the input data and filter the dax table based on the selcted value from the group slicer.

Hope you undersatand and appreciate the continuous help.

Thanks

Re: DAX- To convert a comma delimited string column into a list and filter the Table

eliasayyy — Fri, 08 Sep 2023 07:58:34 GMT

not sure if i understood you correctly but here

Total Amount = IF(ISFILTERED('Table'[Group]),CALCULATE(SUM(TasksSplit[Amount]),TasksSplit[Group] = MAX('Table'[Group])),SUM(TasksSplit[Amount]))

Ranking = IF( ISFILTERED('Table'[Group]), IF( NOT(ISBLANK([Total Amount])), CALCULATE( RANKX( ALLSELECTED(TasksSplit[SingleName]), [Total Amount], , DESC, Dense ), TasksSplit[Group] = MAX('Table'[Group]) ) ),RANKX( ALLSELECTED(TasksSplit[SingleName]), [Total Amount], , DESC, Dense ) )

As you see i am using the group from the main table without any relationships

Final result

Re: DAX- To convert a comma delimited string column into a list and filter the Table

suman1985 — Fri, 08 Sep 2023 08:54:41 GMT

Thanks eliasayyy

I have to really thanks for answering my queries with great patience.We are very close to the required result.

It is working for single select value on slicer.

Its my fault, I didnt explain that slicer should be a multi select valued.

I have more than 1 slicer then result table should filter based on the 3 slicers selected values as shown on screenshot .
Please modify DAX for multiselect on all slicers (ALL, Mutiselect) .It should work for both

Input Data:

Brand	Sub	Event	Tasks	Amount
A	Free	Group1	a,b,c	100
A	Normal	Group2	b,c	150
A	Free	Group3	a,c	200
B	Normal	Group4	b,d	130
B	Free	Group5	a,c,d	50
C	Free	Group6	d	250
C	Free	Group7	c,d	300
D	Normal	Group1	a,d	100