Filter with Text Contains

[AshwinC]

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?

[v-henryk-mstf]

Hi @AshwinC ,

Whether the advice given by @MFelix  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[MFelix · Accepted solution]

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?