Single values, comma separated values in slicers in power bi

user_5709670 0
Viewed 198

I have a table that contains comma-separated values and single values in a column (text data type). I do transformations and make a unique list of all single values and comma-separated values. When I click on the slicer and select any value it shows only one single valued row, not including comma-separated value rows.

enter image description here

Here is my Dataset:

Name    Fax     Phone        Email                  Comments
-----   -----   ------       ------                  --------
Ravi    401852  258525       hi                 Email missing
Raju            754569       gsk@protonmail.com fax missing
Reetu   475875                                   Email Missing, Phone Missing
Heera                                            Fax Missing, Phone Missng, Email Missing
naidu                        naidu@company.com   Fax missing, Phone Missing

Now I am doing transformation on comments and making a unique list like the below by taking a reference from this dataset.

Email Missing
Fax Missing
Phone Missing

I am using this unique list in my slicer to filter the data in a table. But it doesn't fulfill my requirement.

It missing the CSV values and display only single values.

Example: When I select "fax Missing" from the slicer, it shows only

Raju            754569       gsk@protonmail.com fax missing

But I required all the records including comma-separated ones, i.e. I expected the records of Raju, heera and naidu to display.

dax powerbi powerquery
1 Answers

You can use the following measure to filter the fact using unique values from the slicer.

Measure =
VAR _isfiltered =
    ISFILTERED ( Slicer[Slicer.Value] )
VAR _count =
    CALCULATE (
        COUNT ( 'Fact'[Fact.Comments] ),
        FILTER (
            VALUES ( 'Fact'[Fact.Comments] ),
            CONTAINSSTRING ( 'Fact'[Fact.Comments], SELECTEDVALUE ( Slicer[Slicer.Value] ) )
        )
    )
RETURN
    IF ( _isfiltered == TRUE, _count, 0 )

_isfiltered ensures that false is returned when nothing is selected.

CONTAINSSTRING reads through the comma-separated values.

Finally, results are returned as long as there is a selection.

s1

s2

user_111561310
Related