Forum Discussion

94veiga's avatar
94veiga
Icon for Helper II rankHelper II
5 years ago
Solved

Advanced Filtering

Hello!! I have a table like this one:   Worker Department Peter Marketing Jaime Marketing Peter Accounting Alex Human Resources Maria Accounting   I want to filter for ...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi 94veiga ,

     

    Create a new table based on your original table for slicer:

     

    Table2 = UNION(SELECTCOLUMNS('Table',"Category","Worker","Value",'Table'[Worker]),SELECTCOLUMNS('Table',"Category","Department","Value",'Table'[Department]))

     

    Then create two slicers using the new column from the new table, and add visual level filter to them:

     

    Then you can use the following measure:

    Measure =
IF (
    ISFILTERED ( Table2[Value] ),
    SWITCH (
        MAX ( Table2[Category] ),
        "Worker",
            VAR A =
                CALCULATETABLE (
                    VALUES ( 'Table'[Department] ),
                    FILTER ( 'Table', 'Table'[Worker] = MAX ( Table2[Value] ) )
                )
            RETURN
                CONCATENATEX (
                    EXCEPT (
                        CALCULATETABLE (
                            VALUES ( 'Table'[Worker] ),
                            FILTER ( 'Table', 'Table'[Department] IN A )
                        ),
                        VALUES ( Table2[Value] )
                    ),
                    'Table'[Worker],
                    ","
                ),
        "Department",
            CONCATENATEX (
                CALCULATETABLE (
                    VALUES ( 'Table'[Worker] ),
                    FILTER ( 'Table', 'Table'[Department] IN VALUES ( Table2[Value] ) )
                ),
                'Table'[Worker],
                ","
            )
    ),
    CONCATENATEX ( VALUES ( 'Table'[Worker] ), 'Table'[Worker], "," )
)

     

     

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

     

    Best Regards,

    Dedmon Dai

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

94veiga , for that is better you need an independent table with names

 

measure =

var _tab = summarize(filter(Table, Table[Worker] in allselected(Worker[Worker])), Table[Department])
return
calculate(counrows(Table), filter(Table, Table[Department] in _tab))

94veiga's avatar
94veiga
Icon for Helper II rankHelper II
5 years ago

Thanks for your time! I tried to do what you've told me but I think I didn't get it. It is giving me the number of people he is working with but not the rest of the info.

 

Thanks again

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi 94veiga ,

     

    Create a new table based on your original table for slicer:

     

    Table2 = UNION(SELECTCOLUMNS('Table',"Category","Worker","Value",'Table'[Worker]),SELECTCOLUMNS('Table',"Category","Department","Value",'Table'[Department]))

     

    Then create two slicers using the new column from the new table, and add visual level filter to them:

     

    Then you can use the following measure:

    Measure =
IF (
    ISFILTERED ( Table2[Value] ),
    SWITCH (
        MAX ( Table2[Category] ),
        "Worker",
            VAR A =
                CALCULATETABLE (
                    VALUES ( 'Table'[Department] ),
                    FILTER ( 'Table', 'Table'[Worker] = MAX ( Table2[Value] ) )
                )
            RETURN
                CONCATENATEX (
                    EXCEPT (
                        CALCULATETABLE (
                            VALUES ( 'Table'[Worker] ),
                            FILTER ( 'Table', 'Table'[Department] IN A )
                        ),
                        VALUES ( Table2[Value] )
                    ),
                    'Table'[Worker],
                    ","
                ),
        "Department",
            CONCATENATEX (
                CALCULATETABLE (
                    VALUES ( 'Table'[Worker] ),
                    FILTER ( 'Table', 'Table'[Department] IN VALUES ( Table2[Value] ) )
                ),
                'Table'[Worker],
                ","
            )
    ),
    CONCATENATEX ( VALUES ( 'Table'[Worker] ), 'Table'[Worker], "," )
)

     

     

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

     

    Best Regards,

    Dedmon Dai