Forum Discussion
Helper ISpecial filtering
Hello,
I have two tables (TableABC and TableQty) like the ones below, linked by column2.
TableABC
Column 1Column 2
| A | A1 |
| A | A2 |
| A | A3 |
| A | A4 |
| B | B1 |
| B | B2 |
| B | B3 |
| B | B4 |
| C | C1 |
| C | C2 |
| C | C3 |
| C | C4 |
| C | C5 |
TableQty
YearQtyColumn2
| 2021 | 54 | A1 |
| 2021 | 948 | A2 |
| 2021 | 245 | B3 |
| 2021 | 241 | C5 |
| 2020 | 13 | C1 |
| 2020 | 525 | C4 |
| 2020 | 23 | B2 |
| 2019 | 135 | A4 |
| 2019 | 246 | B2 |
| 2019 | 245 | B4 |
| 2019 | 12 | C1 |
| 2019 | 515 | C2 |
| 2019 | 68 | C3 |
I have a matrix which has Column1 and Column2 as rows, Year as Column and Qty as values. I also have Column2 in a segment filter.
I would like that every time I select the filters A1 or A2 or A3 or A4, my matrix reacts like this:
For B1 or B2 or B3 or B4 like this:
I know that we get to the same result if I put Column1 as a segment filter, but in my case it is impossible.
Is there a way to do this?
Thank you very much!
Here is one way to do it. Use Col2 from your ABC table in the slicer and Col2 from the Qty table in the table visual, along with a measure like below to get the shown result.
Show All Col2 Same Col1 =
CALCULATE (
SUM ( TableQTY[Qty] ),
ALL ( TableABC[Column2] ),
VALUES ( TableABC[Column1] )
)Pat
1 Reply
- mahoneypat
Microsoft Employee
Here is one way to do it. Use Col2 from your ABC table in the slicer and Col2 from the Qty table in the table visual, along with a measure like below to get the shown result.
Show All Col2 Same Col1 =
CALCULATE (
SUM ( TableQTY[Qty] ),
ALL ( TableABC[Column2] ),
VALUES ( TableABC[Column1] )
)Pat