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

[suman1985]

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

[suman1985]

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

[suman1985]

Hi @AnthonyGenovese 

Thanks for quick response. 

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

Appreciate the help..

[eliasayyy]

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

[suman1985]

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

[eliasayyy]

Ranking =

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

 if its solved your issue kindly accept it as solution

[suman1985]

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

[eliasayyy]

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])

[suman1985]

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

[eliasayyy · Accepted solution]

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 

 

[suman1985]

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

[eliasayyy]

can you show me what slicer you are using please

[suman1985]

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

[AnthonyGenovese]

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!

[suman1985]

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

[eliasayyy]

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])