Forum Discussion

94veiga's avatar
94veiga
Helper II
5 years ago
Solved

Advanced Filtering

Hello!! I have a table like this one:

 

WorkerDepartment
PeterMarketing
JaimeMarketing
PeterAccounting
AlexHuman Resources
MariaAccounting

 

I want to filter for example by department so if I select Marketing department the result would be Peter and Jaime. Also I want to filter by worker so if I choose Peter the result has to be María as they work in the same department.

 

Is this posible?

 

Thanks in advanced!!

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

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
      Helper 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
        Community 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