Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!!
Solved! Go to 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
@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))
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
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
User | Count |
---|---|
77 | |
75 | |
67 | |
65 | |
47 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |