Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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 1CategoryValueColumn 2
1A3True
2A2True
3A0False
4A0False
5B3True
6B2False
7B0True
8C1True

 

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:

 

CategoryCount
A2
B2
C1

 

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 1CategoryValue
5B3
7B0

 

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.

1 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:

Greg_Deckler_0-1599621307025.png

First visual

Greg_Deckler_1-1599621381962.png

Second visual:

Greg_Deckler_2-1599621427795.png

Selected:

Greg_Deckler_3-1599621464872.png

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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
Super User
Super User

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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 IDCategoryDataInclusion 1Inclusion 2
1A111
2A210
3A301
4B111
5B210
6B301
7C111
8C210
9C301

 

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

CategoryActualInclusion 1Inclusion 2
A322
B322
C322

 

Should I select the cell as below

 

CategoryActualInclusion 1Inclusion 2
A322
B322
C322

 

I want the second table to look like this:

Unique IDCategoryData
1A1
2A2

 

While if I select the cell as below

 

CategoryActualInclusion 1Inclusion 2
A322
B322
C322

 

I want the second table to look like this:

Unique IDCategoryData
4B1
5B2
6B3

 

And if I select the cell as below

 

CategoryActualInclusion 1Inclusion 2
A322
B322
C322

 

I want the second table to look like this:

Unique IDCategoryData
7C1
9C3

 

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?

@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:

Greg_Deckler_0-1599621307025.png

First visual

Greg_Deckler_1-1599621381962.png

Second visual:

Greg_Deckler_2-1599621427795.png

Selected:

Greg_Deckler_3-1599621464872.png

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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..

@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.

Greg_Deckler_0-1599622618954.png

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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
Super User
Super User

@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
Not applicable

@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 IDCategoryData
1A4
2A3
3A0
4B3
5B5
6B0
7C2
8C3
9C0

 

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 IDCategoryDataTo include?
1A41
2A31
3A00
4B31
5B51
6B00
7C21
8C31
9C00

 

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):

CategoryActual CountIncluded
A32
B32
C32

 

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

 

Unique IDCategoryData
1A4
2A3
3A0
4B3
5B5
6B0
7C2
8C3
9C0

 

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

CategoryActual CountIncluded
A32
B32
C32

 

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

 

Unique IDCategoryData
4B3
5B5
6B0

This is what I want

 

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

CategoryActual CountIncluded
A32
B32
C32

 

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

 

Unique IDCategoryData
4B3
5B5

 

I get this.

 

Unique IDCategoryData
4B3
5B5
6B0

 

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

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.