Filter multiple values on multiple columns

[laurensophie25]

Hello!

Here's the example of the data tables that I have -

Status Map:                                   

|ID              | Open        | Closed     | Sold     | Lost    | Awaiting Response   

| 1               | 0              | 1              |   0        |   0       |   1         

| 2               | 1              | 0              |   1        |   1       |   1       

| 3               | 1              | 0              |   0        |  0        |   0      

| 4               | 0              | 0              |   0        |  1        |   0 

I need to create a filter to show a count of ID's per status but that also shows the count of the other status at the same time.

If I filtered on 'Awaiting Response', I can see the count of ID's in Awaiting Reponse but also a count of ID's in the other statuses.

|Status                        | Count of ID's               

| Open                        | 2             

| Closed                      | 1                

| Lost                           | 2

| Awaiting Response   | 2

I cannot unpivot the data based on other calculations & visualisations. 

Thank you!

[Anonymous · Accepted solution]

Hi @laurensophie25 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Click "transform data" to enter the power query, select the [id] column, click "unpivoted other columns", filter [value] column = 1.

(3) We can create a slicer table.

Slicer Table = VALUES('Table'[Attribute])

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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