Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
98 | |
90 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |