Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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:
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.
@Anonymous - See Page 40 and Table 40 of attached PBIX below sig.
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.
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:
First visual
Second visual:
Selected:
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.
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.
@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.
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
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
135 | |
111 | |
98 | |
98 | |
92 |