Slicers, Filter every row in an independent column contained, partially or exactly, in another colum

[Anonymous]

Hi!

I have an unusual issue regarding slicers, I hope someone has already figured this out:

I have a single column in its own table, it is used for a slicer with multiple-selection, so the rows may vary in any way the user clicks in the slicer.

---------------------------------------------------------------------------------

Then, the table that should be filtered has this structure:

So, if I click B on the slicer, I should be able to see every row where B is contained in the column Line Code. 

If I click B and C on the slicer, I would like to see every row where both B and C are contained in the column Line Code. If I click on a third option, let's say J, I would like to see row 2 where those elements are found.

(At the moment I could only reach solutions with an "OR" (UNION) logic, namely, (if I click on the slicer B and C) I see rows where B is contained or where C is contained, which may intersect or not.)

Could anyone be able to solve this scenario? Thanks in advance.

[karnold]

When you copy that code into Desktop you just need the lines after the equal sign. 

You can put the measure in the filter pane and ensure it is not blank. I put it directly in the visual just to easily see the result. 

[Anonymous]

Hi @karnold ,

Thanks, thanks for confirming it!

For some reason still it doesn't work.

• My SingleElementSlicerColumn is identical and indexed.
• The original table is thousands of rows much longer but keeps the same structure and elements. It was also indexed.
• The measure was put in the visual filter for the table visual correctly. 

Measure from powerbi community = 
    VAR _SelectedCodeCount = 
        COUNTROWS(VALUES( 'LineCodeSlicer'[Value] ) )
    VAR _InscopeIndex =
        TREATAS(VALUES('Table'[Index]),'LineCodeSlicer'[Index])
    VAR _IndexCount =
        COUNTX(CALCULATETABLE( 'LineCodeSlicer', _InscopeIndex ),'LineCodeSlicer'[Index])
    RETURN
        IF(
            _SelectedCodeCount = _IndexCount,
            FORMAT( _SelectedCodeCount, "#" ) & (" ")
                & FORMAT( _IndexCount, "#" ), BLANK()
        )

If I put this measure as a column in the table visual, it is blank from every row. 

Once the "IS NOT BLANK" filter is applied in the filter panel, it filters out all:

And this is a card of the measure when the filters are applied (in this case, it is a double selection):

But the rows in the table are still blank, hence, they are filtered out.

How did this work for you? Thanks once more for the help!

[karnold · Accepted solution]

Here is my example file on GitHub. Maybe that will help?

https://github.com/kevarnold972/blogshare/blob/master/Misc/Line%20Code%20Grouping.pbix

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Thanks a lot!

[Anonymous]

Thanks!

[karnold]

I was able to solve this with your test data by modeling another table that splits the line codes into rows and then a measure to count the codes per table row. When this count matches the selected count a non-blank value is returned in the measure. The measure is included in the visual to limit the rows. 

"Table" is your example data with an index column added. LineCodeIndex is a row per the split line code value for an index. Note, no relationship between them.

The measure code, Could return any non-blank value, these values help with debugging:

MEASURE 'Table'[Included] = 
    VAR _SelectedCodeCount =
        COUNTROWS( VALUES( 'LineCodeIndex'[Value] ) )
    VAR _InscopeIndex =
        TREATAS( VALUES( 'Table'[Index] ), 'LineCodeIndex'[Index] )
    VAR _IndexCount =
        COUNTX(
            CALCULATETABLE( 'LineCodeIndex', _InscopeIndex ),
            'LineCodeIndex'[Index]
        )
    RETURN
        IF(
            _SelectedCodeCount = _IndexCount,
            FORMAT( _SelectedCodeCount, "#" ) & " "
                & FORMAT( _IndexCount, "#" )
        )

 Here is the PQ M code to produce the LineCodeIndex:

let
    Source = Table,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Code", "Date"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Line Codes", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Line Codes.1", "Line Codes.2", "Line Codes.3", "Line Codes.4", "Line Codes.5", "Line Codes.6"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Line Codes.1", type text}, {"Line Codes.2", type text}, {"Line Codes.3", type text}, {"Line Codes.4", type text}, {"Line Codes.5", type text}, {"Line Codes.6", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Index"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns1"

[Anonymous]

Hi @karnords !

Thanks for helping me.

The Power Query Part wroks fine and straight forward.

On the other hand, the measure doesn't seem to work me:

MEASURE 'Table'[Included] =

VAR _SelectedCodeCount =

COUNTROWS( VALUES( 'LineCodeIndex'[Value] ) )

VAR _InscopeIndex =

TREATAS( VALUES( 'Table'[Index] ), 'LineCodeIndex'[Index] )

VAR _IndexCount =

COUNTX(
CALCULATETABLE( 'LineCodeIndex', _InscopeIndex ),
'LineCodeIndex'[Index]
)

RETURN

IF(
_SelectedCodeCount = _IndexCount,
FORMAT( _SelectedCodeCount, "#" ) & " "
& FORMAT( _IndexCount, "#" )
)

MEASURE 'Table'[Included] is marked as an error and if a write a simple label such as "Included" nothing happens. What could be happening on this section of the string?

----------------------------------------

I also wanted to confirm with you.... after creating a table and having the measure existing, should I put the new measure in the Filter Field at the rate keeping only those are not blank, or you had another step in mind?

Thanks once more! Have a good day.