Advanced Filtering

[94veiga]

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 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!!

[amitchandak]

@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]

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 · Accepted solution]

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