Slicer to Filter Substring

[Gsar]

Hey Community!

For once again I am in need of your help. 

I have created a measure that allows me to count the specific number of time slots within a time range and converted it to a visual. 

Lets us call this measure time_slot_calculation. 

To make it a bit more visual, imagine that on your x axis you have time (8,9,10,11 etc) and on the y axis the count of time slots.

My fact table, let us call it transactions contains a column with comma separated values. 

See below.

Date	random variable	Time Start	Time End	Week	id	Product
25-06-2020	c1	08:30	10:00	1	1	Meat
26-06-2020	c1	08:00	17:00	1	2	Meat, Beef
27-06-2020	c2	09:00	12:00	2	3	Beef
28-06-2020	c2	09:15	12:30	2	4	Cheese, Meat, Beef
29-06-2020	c1	10:00	11:30	1	5	Beef
30-06-2020	c3	13:15	16:40	3	6	Beef
01-07-2020	c3	12:00	17:00	4	7	Cheese
02-07-2020	c4	10:40	13:35	4	8	Cheese, Meat
02-07-2020	c4	09:40	13:35	4	9	Meat

What I need is a slicer that if I select to slice for Meat the transactions table will filter for all the rows that contains the value Meat.

What I have done so far is to create another table with one column that contains the distinct values of Product.

So, it contains 1 column with values Cheese, Meat and Beef. 

The next think I did was create a measure like the following

Flag 2 = 
CALCULATE (
    COUNTROWS ( 'transactions' ),
    FILTER (
        'transactions',
        SUMX (
            product,
            SEARCH ( product[product], 'transactions'[product], 1, 0 )
        ) > 0
    )
)

That is something that I saw from another post.

When I create the slicer from my product table, it works fine only if I use it straight to my fact table.

When I try to use it to my visual, it does not do anything.

Just to mention, the flag 2 measure, I am using it on the filters and I have set it to 1.

The product table does not have any relationship with the transactions.

Does anyone have any idea of how to solve the problem?

To sum up, I am looking for a solution that will allow me to use the new slicer with the distinct values of the product which wil consequently filter all the rows in the transsactions table that contains the selected value/values and will work on my visual that has a measure on it which is derived from the transactions table.

I apologize for the lengthy post. I hope that my questions is clear.

Best,

G

[ahmedoye · Accepted solution]

I assume that you have a very good hang of DAX. I hope you will be able to adapt this my measure to yours.

Basically, you should start by creating a disconnected table that looks like the one below:

Then write a measure that looks like the below:

Row Count =

VAR SelectedUniqueProduct =

SELECTEDVALUE ( 'Product'[Unique Product] )

VAR ContainSelection =

FILTER (

ALL ( 'Table'[Product] ),

CONTAINSSTRING ( 'Table'[Product], SelectedUniqueProduct )

)

RETURN

CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Product] IN ContainSelection )

My result looks like the below:

If this works for you, kindly mark as solution to allow other community members who may have similar challenges find solutions quickly.

[smpa01 · Accepted solution]

@Gsar

 If I understand the ask correctly, when you select Beef, Cheese, Meat,  the measure should respectively give you 5,3,5.

You can use a measure like this

Measure = 
VAR _1 =
    ADDCOLUMNS (
         'Table',
        "newString", SUBSTITUTE ( 'Table'[Product], ",", "|" )
    )
VAR _2 =
    GENERATE (
        _1,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [newString] ) ),
            "splitString", TRIM ( PATHITEM ( [newString], [Value], TEXT ) )
        )
    )
RETURN
    COUNTX (
        FILTER ( _2, [splitString] = SELECTEDVALUE ( 'ProductSlicer'[Product] ) ),
        [splitString]
    )

[Gsar]

Hey @smpa01 smpa01. 

Thank you for your answer. It what I have been looking for.

[smpa01]

@Gsar  Did you have a chance to look into this?

[bcdobbs]

Another option would be to split out the comma separated list into separate rows using power query.

1) add an index so you can use countdistinct if you need to know original number of rows.

2) Select the comma separated column and in the transform tab click Split Column by Delimiter. Select a comma deliminator. Click the arrow to expand the advanced options and then select rows.

That should make your dax simpler and allow for easy filtering.

[Gsar]

Thank you for replying to my post @bcdobbs . 

I tried your solution as well but things were getting complicated.

Currently I have already built a solution in a certain way and the solution which uses a simple measure is faster and more understandable.

[ahmedoye · Accepted solution]

I assume that you have a very good hang of DAX. I hope you will be able to adapt this my measure to yours.

Basically, you should start by creating a disconnected table that looks like the one below:

Then write a measure that looks like the below:

Row Count =

VAR SelectedUniqueProduct =

SELECTEDVALUE ( 'Product'[Unique Product] )

VAR ContainSelection =

FILTER (

ALL ( 'Table'[Product] ),

CONTAINSSTRING ( 'Table'[Product], SelectedUniqueProduct )

)

RETURN

CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Product] IN ContainSelection )

My result looks like the below:

If this works for you, kindly mark as solution to allow other community members who may have similar challenges find solutions quickly.

[Gsar]

Thank you for replying to my post @ahmedoye. 

In some cases your solution is a bit slow, specifically when I enable two way filtering, but nevertheless it works and does what it is supposed to do 🙂