Forum Discussion
Matrix that filters visuals based on true/false column
Hi, I have this table on PowerQuery with products and different attributes designated by true/false (I could change it to 1/null, 1/0 or whatever) :
| Product | Type of product | Attribute A | Attribute B | Attribute C |
| 1 | Chair | True | True | False |
| 2 | Chair | True | True | True |
| 3 | Table | False | False | False |
I want to create a Matrix on PBI that counts the number of products with each attribute like this :
| Type of product | Attribute A | Attribute B | Attribute C |
| Chair | 2 | 2 | 1 |
| Table | 0 | 0 | 0 |
I've managed to get this Matrix but the last issue I have is that, when I click on a cell, for example the 2 chairs with attribute A, the matrix doesn't interact with the rest of my visuals to filter only the 2 chairs with attribute A on the whole page. What it does instead instead is filter only the products labelled "Chair". Does anyone have an easy solution that doesn't include creating a table for each attribute? Thank you!
Hi Jdtz
To analyze your data in an effective way, you have to unpivot the table and bring it from horizontal to vertical form.The steps that can be applied to achieve this in PQ:
After applying you can use a Dax measure for counting the rows with "true" :
count attributes = CALCULATE(COUNTROWS('Table'),'Table'[Value]=TRUE())+0Use this measure with a matrixmore information about unpivoting:
https://www.youtube.com/watch?v=URAJ_aDiREEFile with an example attached, you can download it
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
1 Reply
- Ritaf1983
Super User
Hi Jdtz
To analyze your data in an effective way, you have to unpivot the table and bring it from horizontal to vertical form.The steps that can be applied to achieve this in PQ:
After applying you can use a Dax measure for counting the rows with "true" :
count attributes = CALCULATE(COUNTROWS('Table'),'Table'[Value]=TRUE())+0Use this measure with a matrixmore information about unpivoting:
https://www.youtube.com/watch?v=URAJ_aDiREEFile with an example attached, you can download it
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.