Forum Discussion
Multi-level Filter for tables
I have trouble create a table with varying number of columns.
Say I have the following dataset containing some sales data
Customer ID | Country | Customer group | Number of products bought
How do I create a table where I can use slicer such that with I select all customer ID and customer group, the two columns will disappear in the table and leaving only country | number of products?
Hi tmv3v ,
If you wanna use a table visual, based on my research, it is not supported yet for now. But you can post an idea here.
https://ideas.powerbi.com/forums/265200-power-bi-ideas
But in the meanwhile ,I can show you a workaround where you can make 2 columns disappear when you select all the the columns in a slicer.
Steps are as below:
- Create a measure as below:
Measure = IF(ISFILTERED('Table'[Customer group])&&CALCULATE(COUNT('Table'[Customer group]),ALL('Table'))=CALCULATE(COUNT('Table'[Customer group]),ALLSELECTED('Table'))&&MAX('Table (2)'[Column1])="Customer group",BLANK(),IF(ISFILTERED('Table'[Customer ID])&&CALCULATE(COUNT('Table'[Customer ID]),ALL('Table'))=CALCULATE(COUNT('Table'[Customer ID]),ALLSELECTED('Table'))&&MAX('Table (2)'[Column1])="Customer ID",BLANK(),SUM('Table'[Number of products bought])))2.Choose a matrix, and pick the fields as below:
3, Finally when you select all the columns ,they would “disappear ” in the Matrix.
For the related .pbix file,you can turn to URL:
Hope this would help.
Best Regards,
Kelly
1 Reply
Hi tmv3v ,
If you wanna use a table visual, based on my research, it is not supported yet for now. But you can post an idea here.
https://ideas.powerbi.com/forums/265200-power-bi-ideas
But in the meanwhile ,I can show you a workaround where you can make 2 columns disappear when you select all the the columns in a slicer.
Steps are as below:
- Create a measure as below:
Measure = IF(ISFILTERED('Table'[Customer group])&&CALCULATE(COUNT('Table'[Customer group]),ALL('Table'))=CALCULATE(COUNT('Table'[Customer group]),ALLSELECTED('Table'))&&MAX('Table (2)'[Column1])="Customer group",BLANK(),IF(ISFILTERED('Table'[Customer ID])&&CALCULATE(COUNT('Table'[Customer ID]),ALL('Table'))=CALCULATE(COUNT('Table'[Customer ID]),ALLSELECTED('Table'))&&MAX('Table (2)'[Column1])="Customer ID",BLANK(),SUM('Table'[Number of products bought])))2.Choose a matrix, and pick the fields as below:
3, Finally when you select all the columns ,they would “disappear ” in the Matrix.
For the related .pbix file,you can turn to URL:
Hope this would help.
Best Regards,
Kelly