Forum Discussion
filter a table using the selected value from another table
I figured out myself Filtered Category Count =
VAR SelectedCategories = VALUES('ID category filter'[Category])
RETURN
CALCULATE(
DISTINCTCOUNT('ID and category'[ID]),
FILTER(
'ID and category',
'ID and category'[Category] IN SelectedCategories
)
)
Hi,
Sorry but it is not clear for me to understand what is the expected outcome.
In the image you shared, the current one shows with a slicer-visual, but desired one shows no-slicer-visual.
And, please share your sample pbix file.
v-menakakota and Jihwan_Kim ,
I have three tables ID only, ID and category, ID and category filter.
ID is primary table that is connected to other two tables with 1 to many relationship.
I to display category and counts from "ID and category" table but use filter from "ID and category filter" table. When I select Category A as a filter, as shown in the screenshot above, currently other categories and count show up as well, as they are connected to main primary ID table by ID. I would like to create a filter in such a way that when I select "Category A" as a filter only Category A and count of ID display in the table. Below is my mock data, which is what above screenshots show. If you take a closer look, the desired result is "filter being selected A" and only A and count getting displayed in the table.
ID and category filter table
| ID | Category |
| 1 | A |
| 1 | B |
| 1 | C |
| 3 | A |
| 3 | B |
| 4 | C |
| 5 | D |
| 6 | A |
| 7 | B |
| 7 | C |
| 7 | D |
| 8 | E |
| 3 | A |
| 2 | B |
| 2 | C |
| 2 | D |
| 9 | E |
| 9 | F |
| 9 | G |
| 4 | A |
| 4 | B |
ID and category table
| ID | Category |
| 1 | A |
| 1 | B |
| 1 | C |
| 3 | A |
| 3 | B |
| 4 | C |
| 5 | D |
| 6 | A |
| 7 | B |
| 7 | C |
| 7 | D |
| 8 | E |
| 3 | A |
| 2 | B |
| 2 | C |
| 2 | D |
| 9 | E |
| 9 | F |
| 9 | G |
| 4 | A |
| 4 | B |
ID table
| ID |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
17 |