Forum Discussion
Slicer to Filter Substring
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
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 ))RETURNCALCULATE ( 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.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] )
7 Replies
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 ))RETURNCALCULATE ( 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.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.
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] )
