Show distinct values filtered out by slicers and/or table selection

[richarp]

Hi all,

I have quite a specific requirement and I'm not sure how best to approach, so any advise would be welcome.

I'm analysing mapped drives and folders across an organisation.  I have one table which holds the drive data at as user level and then a linked table which holds the employee data so I can add slicers for filtering by department, team etc.

I have created a simple dashboard that has some slicers and a table to show the filtered data.  As well as using the slicers, the user might also click on a user in the table to further filter data.

Here is an example of the dashboard in use:

As I've indicated, what I would like to be able to show is any drive letter that is unused based on any filtering (slicer or user clicking in table).

Does anyone have any suggestions I can investigate.  I've spent over a day searching forums but have had no luck to date.

Many thanks in advance

[v-stephen-msft · Accepted solution]

Hi @richarp ,

1.Create a separate table by entering data.

2.The relationship is this.

3.Create the measure. Then create a table visual with [Drive] column in Table (3), put the meaure into Filters. Set show items when the value is 0.

Measure = IF(MAX('Table (3)'[Drive]) IN VALUES('Table'[Drive]),1,0)

 

4.Now when CompanyA is selected, drives R, S, T and W are filtered.

5.If you want to filter by clicking users, you can only put a user column in the table. If you put other columns, such as user and drive, because the filter is based on rows, the stand-alone User1 and the data of User1 and R can be filtered out.

You can check details from the attachment.

Best Regards,

Stephen Tao

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

[richarp]

Thanks @v-stephen-msft - that worked perfectly!

[amitchandak]

@richarp , The text on image and description you have given, I am not able to connect two

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Refer if you want to exclude selected values - https://www.youtube.com/watch?v=lOEW-YUrAbE

[richarp]

Hi @amitchandak   

I have 2 tables: 

One containing information about user drives, for example:

User	Drive
User1	R
User1	S
User1	T
User2	R
User2	S
User2	W
User3	U
User3	S
User3	Q

And one containing information about the employees:

User	Company	Department
User1	CompanyA	Department1
User2	CompanyA	Department1
User3	CompanyB	Department3

The drive data is the main table displayed on the dashboard.

I have a slicer linked to the Company and a slicer linked to the Department.

Let's say I select CompanyA in the company slicer.  This would filter the drive table like this:

User	Drive
User1	R
User1	S
User1	T
User2	R
User2	S
User2	W

I would like to display a visual which shows unused drive letters based on the alphabet.

So in this scenario it would display:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

U

V

X

Y

Z

This is because drives R, S, T and W are used by those filtered employees.

Additionally, the dashboard user might then select User1 in the table.  In this situation the spare letters should only filter out R, S and T as they are the only drives used by User1.

So basically based on any filter via slicer or table row selection, I want to display the letters not used as drive letters.

Does that help?