Forum Discussion
Count with slicers from 2 tables
| Table A | Table B | |
| Product code | Product code | |
| SAP Id | Category A | |
| Brand | Category B | |
| Category C |
I have above 2 tables.
Both tables are linked via a one-to-one relationship based on column [Product code]
I want a table visual, with a column [SAP Id].
And, there should be a subtotal count of the number of [SAP Ids] in that visual.
This should be the result:
| SAP Id | Count |
| A | 4 |
| B | 4 |
| C | 4 |
| D | 4 |
The [SAP Ids] and the count should be updated based on the slicers I select from table A and table B.
Problem is that, currently, if I select a slicer from table B, the count is corrected, but the column [SAP Id] still shows all of the SAP Ids, not considering the filter from table B:
This is the (incorrect) result I'm getting:
| SAP Id | Count |
| A | 3 |
| B | 3 |
| C | 3 |
| D | 3 |
But, I want a visual as follows (because D is filtered out because of the filter from table B).
| SAP Id | Count |
| A | 3 |
| B | 3 |
| C | 3 |
2 Replies
- bhanu_gautam
Super User
TR_Belgium , First create a combined table either by using Merge in Power Query or by DAX
Go to model new table
CombinedTable =
NATURALINNERJOIN(
SELECTCOLUMNS(TableA, "Product code", TableA[Product code], "SAP Id", TableA[SAP Id], "Brand", TableA[Brand]),
SELECTCOLUMNS(TableB, "Product code", TableB[Product code], "Category A", TableB[Category A], "Category B", TableB[Category B], "Category C", TableB[Category C])Then create measure
SAPIdCount = COUNTROWS(CombinedTable)
)Thanks for the answer, would you know if it is also possible to have a solution without merging tables?
