Forum Discussion
Filter with Text Contains
Hi All,
I have Column which has muliple Values
| Option Type | Date1 | Date 2 |
| Option1, Option2, Option3 | 10/02/2022 | 12/02/2022 |
| Option1, Option3 | 15/01/2022 | 16/01/2022 |
| Option2 | 18/01/2022 | 30/01/2022 |
| Option1 | 12/01/2022 | 30/01/2022 |
I need to create report with a Slicer of Unique Values
| Type | Count | Average Days (Days1 to Days2) |
| Option 1 | 3 | 3 |
| Option 2 | 2 | 4 |
| Option 3 | 2 | 5 |
I need to able to summarize the table, like Average Days between Days1 and Days2, Count of Rows etc.
Is there a way where i can achieve this?
Hi AshwinC ,
Believe the best option is to make a transformation on the query editor and split those options by row:
This will allow to have the final result directly on your table.
If this is not possible then you can try to do the following:
- Create a disconnected table with all your options
- Then create the following measure
CountRows = COUNTROWS ( FILTER ( ADDCOLUMNS ( 'Table (2)', "StringFind", CONTAINSSTRING ( 'Table (2)'[Option Type], SELECTEDVALUE ( Options[Options] ) ) ), [StringFind] <> BLANK () ) )Regarding the second value you present how is it calculated?
2 Replies
Hi AshwinC ,
Believe the best option is to make a transformation on the query editor and split those options by row:
This will allow to have the final result directly on your table.
If this is not possible then you can try to do the following:
- Create a disconnected table with all your options
- Then create the following measure
CountRows = COUNTROWS ( FILTER ( ADDCOLUMNS ( 'Table (2)', "StringFind", CONTAINSSTRING ( 'Table (2)'[Option Type], SELECTEDVALUE ( Options[Options] ) ) ), [StringFind] <> BLANK () ) )Regarding the second value you present how is it calculated?
