Filter comma separated columns from slicer and show description columns as text

[alsm]

I have been given a large data set (number of rows and columns) in an excel. It looks approximately like

Project Name	Project Description	Launch Countries	Area	Sub-Area	Summary	Information System	..	Description-50
P1	This project involved launch of ...	USA, Mexico	Oil, Gas & Consumable Fuels	Oil & Gas Drilling , Oil & Gas Equipment & Services, Oil & Gas Exploration & Production
P2	This project involved modification ...	China, Japan	Metals & Mining	Aluminum, Diversified Metals & Mining , Copper, Gold, Silver
P3	This project involved...	Japan	Oil, Gas & Consumable Fuels	Oil & Gas Exploration & Production, Oil & Gas Refining & Marketing
P4	This project involved...	USA	Transportation	Marine, Rail, Cargo Ground, Cargo Air, Passenger Ground
P5	This project involved...	Thailand, Singapore	Oil, Gas & Consumable Fuels	Oil & Gas Equipment & Services, Oil & Gas Exploration & Production
P6	This project involved...	Japan, India, Mexico, UAE, Spain, UK, Brazil	Metals & Mining	Aluminum, Diversified Metals & Mining , Copper, Gold, Silver

Now I have been tasked to create a presentation page (formatted text, rather than numbers) as follows

1. User filters on columns: Launch Countries, Area, Sub-Area using a slicer. This will reduce the number of rows baed on selected value

2. Now take values in columns like: Summary, Information System, ... Description-50 to create a page that looks like a presentation

e.g.  

Summary: bullet-1 (from filtered first row), bullet-2 (from filtered first row),...

...

...

Description-50: bullet-1 (from filtered first row), bullet-2 (from filtered first row),...

So two things

1. Multiple colums which are comma separated are filtered based on slicer values

2. show text in formatted way from each of the column

(then this page will be exported and ssent out)

I want to avoide using M-language to split columns (like 'Launch Countries') as number of rows will explode and there are 6 such columns.

Not sure, how is it possible. Would appreciate any pointers.

Thanks

[tamerj1 · Accepted solution]

Hi @alsm 

First you need to create a slicer table for each column (in case the slicer table is not already available or cannot be created manually) as follows 

S_Table =
DISTINCT (

SELECTCOLUMNS (

GENERATE (

VALUES ( 'Table'[Column] ),

VAR Items = SUBSTITUTE ( 'Table'[Column], ",", "|" )

VAR Length = COALESC ( PATHLENGTH ( Items ), 1 )

VAR T = GENERATESERIES ( 1, Length, 1 )

RETURN 

SELECTCOLUMNS ( "@Item", PATHITEM ( Items, [Value] )

),

"Item", [Items]
)

)

The filter which you can for example use to filter the table visual would something like 

COUNTROWS (

FILTER ( 

'Table',
PATHCONTAINS ( SUBSTITUTE ( 'Table'[Column], ",", "|" ), SELECTEDVALUE ( S_Table[Item] )
)
)

of course other columns need to be all added to FILTER using && operator 

[alsm]

Hi @tamerj1, it works but I have no clue.

Its a bit frustrating but I don't know how to debug step-by-steb in DAX (unlike in a programming language). 

Finally I tried a simpler variant.

I went to 'Tranform Data' -> Referenced the 'Table' --> Removed all columns except 'Column' -->Trim.

This new table I used as a slicer and combined with your filter code to create filter for 'single selection'.

However, In code

COUNTROWS (

FILTER ( 

'Table',
PATHCONTAINS ( SUBSTITUTE ( 'Table'[Column], ",", "|" ), SELECTEDVALUE ( S_Table[Item] )
)
)

If I replace SELECTEDVALUE with ALLSELECTED, it does not work

Thank you for your help

[tamerj1 · Accepted solution]

Good morning @alsm 

Yes you are right. I should have written the code that deals with multiple selections but I was feeling lazy last night 😅

please try

=
COUNTROWS (
FILTER (
'Table',
NOT ISEMPTY (
FILTER (
VALUES ( S_Table[Item] ),
PATHCONTAINS ( SUBSTITUTE ( 'Table'[Column], ",", "|" ), S_Table[Item] )
)
)
)
)

[alsm]

Good morning @tamerj1 

Thank you for spoon feeding me! 

I just cannot seem to get my head around how this (and previous) filter works.

Countrows returns the number of rows selected in slicer. However, in my head I would have expected some kind of list of index True/False corresponding to each row e.g. True,False,False,True and it shows the True ones. Apparently, it works differntly here and I don't seem to get it 😞

[tamerj1]

@alsm 

I guess it's time when you have to place some screenshots so we remain on the same page 

[alsm]

oh, wow.. thank you .. i will try to debug (understand) the code and run it

[tamerj1 · Accepted solution]

Hi @alsm 

First you need to create a slicer table for each column (in case the slicer table is not already available or cannot be created manually) as follows 

S_Table =
DISTINCT (

SELECTCOLUMNS (

GENERATE (

VALUES ( 'Table'[Column] ),

VAR Items = SUBSTITUTE ( 'Table'[Column], ",", "|" )

VAR Length = COALESC ( PATHLENGTH ( Items ), 1 )

VAR T = GENERATESERIES ( 1, Length, 1 )

RETURN 

SELECTCOLUMNS ( "@Item", PATHITEM ( Items, [Value] )

),

"Item", [Items]
)

)

The filter which you can for example use to filter the table visual would something like 

COUNTROWS (

FILTER ( 

'Table',
PATHCONTAINS ( SUBSTITUTE ( 'Table'[Column], ",", "|" ), SELECTEDVALUE ( S_Table[Item] )
)
)

of course other columns need to be all added to FILTER using && operator