How to get a second visual to change based on selection in the first visual

[Anonymous]

Hi everyone,

I have a question which I have been unable to resolve despite a long time searching around, so I decided to try asking on this forum.

Currently, I have a data set structured as shown:

Column 1	Category	Value	Column 2
1	A	3	True
2	A	2	True
3	A	0	False
4	A	0	False
5	B	3	True
6	B	2	False
7	B	0	True
8	C	1	True

For visualisation purpose, I want a visual table with the number of entries in each category to be counted only if column 2 is true, thus giving me this:

Category	Count
A	2
B	2
C	1

From this, I will like to know if it is possible to click, on say the count next to B, so 3rd row and 2nd column in the above table, it will filter a second visual table to give the following values

Column 1	Category	Value
5	B	3
7	B	0

Currently, I am able to get the first visual table, that is the 2nd table above by converting column 2 to boolean, but I am not able to get the 3rd table to change via by selection in the 2nd table.

Help will be appreciated on this.

[Anonymous]

As a footnote, I checked with a person at my workplace and the problem requires unpivoting of the data, in order to get what I want.

[Greg_Deckler]

@Anonymous - See Page 40 and Table 40 of attached PBIX below sig.

[Anonymous]

@amitchandak 

Thank you for your advice, but for my actual dataset, I have multiple rows denoting whether to show the data, so such an approach is infeasible for me as this will filter out too much data.

Using my example above, my data set will more properly look like this:

Unique ID	Category	Data	Inclusion 1	Inclusion 2
1	A	1	1	1
2	A	2	1	0
3	A	3	0	1
4	B	1	1	1
5	B	2	1	0
6	B	3	0	1
7	C	1	1	1
8	C	2	1	0
9	C	3	0	1

For which using the table visualisation, I am able to get

Category	Actual	Inclusion 1	Inclusion 2
A	3	2	2
B	3	2	2
C	3	2	2

Should I select the cell as below

Category	Actual	Inclusion 1	Inclusion 2
A	3	2	2
B	3	2	2
C	3	2	2

I want the second table to look like this:

Unique ID	Category	Data
1	A	1
2	A	2

While if I select the cell as below

Category	Actual	Inclusion 1	Inclusion 2
A	3	2	2
B	3	2	2
C	3	2	2

I want the second table to look like this:

Unique ID	Category	Data
4	B	1
5	B	2
6	B	3

And if I select the cell as below

Category	Actual	Inclusion 1	Inclusion 2
A	3	2	2
B	3	2	2
C	3	2	2

I want the second table to look like this:

Unique ID	Category	Data
7	C	1
9	C	3

So far, I am only able to get the 2nd requirement, that is the general filter by category. I was wondering if there is a way to structure or script it such that I am able to get as above. 

Apologies for being incomplete, it did not occur to me when I raised that question initially that the inclusion of multiple exclusion criteria will greatly complicate solutions.

@Greg_Deckler 

Unfortunately, I am not able to open the file due to version conflicts, and I am not able to update my power BI due to IT policy.

May I know what you did, perhaps via screenshots?

My current power BI version is 2.81.5831.761 if it is a relatively recent functionality.

By the way, may I know what the etiquette is should I have other questions? Do I raise a new topic for each question or is it better to add on below?

[Greg_Deckler · Accepted solution]

@Anonymous - Generally better to close out one question with a Solution marked and ask a new question, refer to the post if necessary.

Here it is in pictures:

The table:

First visual

Second visual:

Selected:

[Anonymous]

@Greg_Deckler 

Thank you for the diagrams, this made it very clear.

As a stopgap, I think this can be implemented in my current dashboard, though I was wondering if there is a way to have it as outlined in my previous post (message number 6), as I have multiple selection criteria and this will necessitate multiple tables.

Once again, I have to apologise for not being complete, as I was under the impression when I first posted the question that the solution can be scaled up with multiple columns with multiple selection criteria and counts without introducing new tables, hence omitting that piece of information..

[Greg_Deckler]

@Anonymous - Well, unfortunately I'm not sure of the solution there. If you select the Column in my solution you get this, which I don't think is what you want because it is the same thing? Sorry, I'm not entirely clear on that set of requirements.

[Anonymous]

@Greg_Deckler 

Apologies for the confusion on my part, as I was not complete in my initial question. This was because I was under the mistaken impression then that the solution for a single selection column works for multiple selection columns, which upon testing I realise is not the case.

The post here https://community.powerbi.com/t5/Desktop/How-to-get-a-second-visual-to-change-based-on-selection-in-... is what I actually want, that is how to extend the table if I have multiple columns depicting multiple selection criteria. Your suggestion works wonderfully for a single selection column, which I can extend to multiple tables if I have any, but I was wondering if there is anyway I can set the tables up to work in the way I depicted in the link above. Should this be possible, I can dispense with multiple tables and just present only one table for the end-viewer.

[amitchandak]

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

See if this can help

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough
Appreciate your Kudos.

[Anonymous]

@amitchandak 

Due to confidentiality reasons, I am not able to provide any data, but I will try rephrasing it.

So, I have a database from my reference file structured as below

Unique ID	Category	Data
1	A	4
2	A	3
3	A	0
4	B	3
5	B	5
6	B	0
7	C	2
8	C	3
9	C	0

Due to the nature of the product, those with data = 0 are to be ignored. I have inserted a column of 1s and 0s for this, giving me the following transformed dataset, as I was not able to get the count with a boolean column.

Unique ID	Category	Data	To include?
1	A	4	1
2	A	3	1
3	A	0	0
4	B	3	1
5	B	5	1
6	B	0	0
7	C	2	1
8	C	3	1
9	C	0	0

Now, when I use this data in a summary table visualisation, I get the following table (excluding those I have marked as 0, i.e. not to be considered):

Category	Actual Count	Included
A	3	2
B	3	2
C	3	2

Next, I set up a visualisation with the details, as follows:

Unique ID	Category	Data
1	A	4
2	A	3
3	A	0
4	B	3
5	B	5
6	B	0
7	C	2
8	C	3
9	C	0

When I click on the cell next to B in the first table that is the cell with text highlighted in green and bold,

Category	Actual Count	Included
A	3	2
B	3	2
C	3	2

the 2nd table is filtered to show only those which are category B, as in below. 

Unique ID	Category	Data
4	B	3
5	B	5
6	B	0

This is what I want

However, when I want to select those that are B and considered as below,

Category	Actual Count	Included
A	3	2
B	3	2
C	3	2

 instead of getting this (which is what I want),

Unique ID	Category	Data
4	B	3
5	B	5

I get this.

Unique ID	Category	Data
4	B	3
5	B	5
6	B	0

So, I was wondering if there is a way to configure such that I can get the result as wanted.

I hope this makes it clearer

[amitchandak]

@Anonymous , To include is a column. You can use page level filter and select 1. [To include]>0

refer

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter