Forum Discussion
Cross filtering within the same table using another column
Hi, I would like to know if it's possible to cross filter within the same table but using another column. More specifically, my table looks like this:
| Customer | Activity Type | Activity Result |
| C1 | T1 | R1 |
| C1 | T2 | R2 |
| C1 | T3 | R3 |
| C2 | T1 | R4 |
| C2 | T2 | R5 |
| C3 | T1 | R6 |
| C3 | T2 | R7 |
| C3 | T3 | R8 |
| C3 | T4 | R9 |
If I have this entire table in a table visual, and some other visuals like bar charts pie charts on the same page, then traditionally, if i click on "R9" in the table, cross filtering will make it work like only the last line of the table "C3 T4 R9" will appear on any other visuals. However, I want the cross filtering to work like it is based on the "Customer" column in the same row, not only just based on the "Result" column. So the resulting filter result would be showing all four lines of related to C3. Is that possible?
Thank you for any help.
Hi gsxr ,
You can create a measure that aggregates based on a unique item in a field/column using ALLEXCEPT but other than that I can't think of a way for Power BI to do it automatically.
Here's a sample measureRow count by Customer = CALCULATE ( COUNTROWS ( Data ), ALLEXCEPT ( Data, Data[Customer] ) )
As shown the screenshot below, C3 has for rows. The other visuals show this count even if only the row containing C3/T1/R6 is clicked.
1 Reply
Hi gsxr ,
You can create a measure that aggregates based on a unique item in a field/column using ALLEXCEPT but other than that I can't think of a way for Power BI to do it automatically.
Here's a sample measureRow count by Customer = CALCULATE ( COUNTROWS ( Data ), ALLEXCEPT ( Data, Data[Customer] ) )
As shown the screenshot below, C3 has for rows. The other visuals show this count even if only the row containing C3/T1/R6 is clicked.