Multiple Column Filter

Hello I am attempting to create a filter based on the below table. The goal would be create a filter with all the distinct values in the column Manager1 and use that as a filter for all four columns...

Need Help

Anonymous

1 year ago

Hi, arosenberg

Thank you for your swift response. Based on the data structure you’ve provided, I have two proposals:

Before we begin with either option, we need to prepare the slicer. Therefore, I have created the following calculation table to generate columns for all managers, which will serve as the slicer.

Table 2 = 
 VAR n1=SELECTCOLUMNS('Table',"1",'Table'[Manager1])
 VAR n2=SELECTCOLUMNS('Table',"1",'Table'[Manager2])
 VAR n3=SELECTCOLUMNS('Table',"1",'Table'[Manager3])
 VAR n4=SELECTCOLUMNS('Table',"1",'Table'[Manager4])
RETURN DISTINCT(UNION(n1,n2,n3,n4))

Of course, this step's calculation table can also be processed using Power Query. Below are the detailed M language instructions:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE1VcFTSUTI0AhLOOZmpeSVgfkBRfkppMoRtAsQWQOybmJeYnlrkiGA6IZjOCKaLUqwOHuOdkIx3otx4kCJjE+zGO0KNtsBhkI6SAhijGAhSYWqGMNAZzb1GeqZA0hxMYphE0DQ3JNNA2owpMg05qkByhiSZBpIyt0CY5oJmGlrQ4YoODHNdgUKWBghzXdH8jGauK4Lpht2hIGFDQ+yB6ESiQ2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [em = _t, #"em #" = _t, Client = _t, Product = _t, Hours = _t, #"Sum Hours" = _t, Manager1 = _t, Manager2 = _t, Manager3 = _t, Manager4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"em", type text}, {"em #", Int64.Type}, {"Client", type text}, {"Product", type text}, {"Hours", type number}, {"Sum Hours", type number}, {"Manager1", type text}, {"Manager2", type text}, {"Manager3", type text}, {"Manager4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"em #", "Client", "Product", "Hours", "Sum Hours"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"em"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"em", "Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns1")
in
    #"Removed Duplicates"

1.For the first proposal:

Firstly, you will need to select these fields in Power Query and then merge them.

Secondly, please use the following measures and apply them to the filters of the visual objects:

MEASURE =
IF (
    ISFILTERED ( 'Table 2'[1] ),
    IF ( CONTAINSSTRING ( MAX ( 'Table'[Merged] ), MAX ( 'Table 2'[1] ) ), 1, 0 ),
    1
)

Here are the final results:

2.For the second proposal:

You can directly use the following measures and apply them to the filters of the visual objects:

Measure2 =
VAR cc1 =
    MAX ( 'Table 2'[1] )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[1] ),
        IF (
            CONTAINS ( 'Table', 'Table'[Manager1], cc1 )
                || CONTAINS ( 'Table', 'Table'[Manager2], cc1 )
                || CONTAINS ( 'Table', 'Table'[Manager3], cc1 )
                || CONTAINS ( 'Table', 'Table'[Manager4], cc1 ),
            1,
            0
        ),
        1
    )