filter a table using the selected value from another table

[bharosha]

I have a table that contains

ID	Category
1	A
1	B
2	C
3	A
3	B
4	C
4	D
4	A
5	B

I have another table that is setting as a filter in the entire page, which is generating a value of Category. I would like to create a table that shows me number of ids per category of selected value of a category from another table. 

Currently I created a table, category and count of ID

A  3

B  3

C  2

I would like this above table to show only the value selected in other table filter. For example if A is selected, that the above table should show

A 3. 

Currently I created a category = selected value (another category table)

But this formula is stating false when one id is under two categories. 

[bharosha · Accepted solution]

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

        )

    )

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I assume your expected result is showing table visualization with a slicer. Please check the below picture and the attached pbix file.

expected result measure: = 
COUNTROWS(Data)

[bharosha]

Here are some screenshots of what I have for dummy data and what I want to get out of. currentlydesired

[v-menakakota]

Hi  @bharosha  ,
Thanks for reaching out to the Microsoft fabric community forum. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team  

[Jihwan_Kim]

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.

[bharosha]

@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