Forum Discussion
Creating Slicer for multiple coulmns
| States | April | April | Var. | Var. | YTD-April | YTD-April | Var. | Var. | May | May | Var. | Var. | YTD-May | YTD-May | Var. | Var. |
| 18-19 | 19-20 | No. | % | 18-19 | 19-20 | No. | % | 18-19 | 19-20 | No. | % | 18-19 | 19-20 | No. | % | |
| MAH | 22 | 167 | 145 | 87% | 22 | 167 | 145 | 87% | 22 | 167 | 145 | 87% | 22 | 167 | 145 | 87% |
| RAJ | 33 | 105 | 72 | 69% | 33 | 105 | 72 | 69% | 33 | 105 | 72 | 69% | 33 | 105 | 72 | 69% |
| DEL-NCR | 56 | 90 | 34 | 38% | 56 | 90 | 34 | 38% | 56 | 90 | 34 | 38% | 56 | 90 | 34 | 38% |
I want to create a slicer for multiple columns. If I select April or May or any other month, the table should display all the columns of that particular month.
Please help me with this issue.
Thank you in advance.
4 Replies
Anonymous
It seems impossible to use slicer with your current table format, and power bi does not support sub-headings. You can change the table into something like this, then create a slicer with Month column.States Month
18-19 19-20 No.% %
YTD18-19 YTD19-20 YTDNO. YTD% MAHH April
22 167 145 ... ... ... RAJ April 33 105 72 ... DELLNCRR April 56 90 34 ... MAHH May ... ... ... ... RAJ May ... ... ... ... DELLNCRR May PaulZhengg _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous , you can use time intelligence and date table
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) var = divide([MTD Sales] -[last year MTD Sales],last year MTD Sales) YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Var = divide([YTD Sales]-[Last YTD Sales],[Last YTD Sales])You can also refer :https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Anonymous ,
If you want to use multiple columns in slicer so use "Hierarchical Slicer" from Custom visual or If you are using latest version of PBI Desktop so Slicer itself has an ability to take multiple columns. (Refer the below screen shot)
