Forum Discussion
Switch Parameters
Hi All,
I have a breakdown per product line & governorate as number of units and another view per values. I need to replcae the calendar filter with a slicer to switch between PTD, YTD, MTD, This week & I need this slicer to filter both number of units a values charts, is iy possible
I know I can use the filed parameter to do this, but If I used it , I will create 2 slicers 1 for the number of units & 1 for the values. Is there a smarter solution
| Product | Units | Unit Price | Value | Date | Governorate |
| Apple | 3 | 4 | 12 | 1/2/2024 | Cairo |
| Banana | 7 | 1 | 7 | 1/2/2024 | Cairo |
| Orange | 18 | 2 | 36 | 1/2/2024 | Cairo |
| Apple | 6 | 4 | 24 | 1/3/2024 | Cairo |
| Banana | 45 | 1 | 45 | 1/3/2024 | Giza |
| Orange | 67 | 2 | 134 | 1/3/2024 | Giza |
| Apple | 2 | 4 | 8 | 1/4/2024 | Giza |
| Banana | 45 | 1 | 45 | 1/4/2024 | Giza |
| Orange | 67 | 2 | 134 | 1/4/2024 | Giza |
| Apple | 89 | 4 | 356 | 1/5/2024 | Cairo |
| Banana | 5 | 1 | 5 | 1/5/2024 | Assiout |
| Orange | 24 | 2 | 48 | 1/5/2024 | Assiout |
| Apple | 56 | 4 | 224 | 1/6/2024 | Assiout |
| Banana | 78 | 1 | 78 | 1/6/2024 | Assiout |
| Orange | 44 | 2 | 88 | 1/6/2024 | Assiout |
| Apple | 66 | 4 | 264 | 1/7/2024 | Cairo |
| Banana | 32 | 1 | 32 | 1/7/2024 | Giza |
| Orange | 66 | 2 | 132 | 1/7/2024 | Assiout |
TIA
Hi Asmaa-elsheikh,
Depending on your requirements, we recommend that you create two additional date tables and manage the relationship between them.
Please follow my steps:
- Create a new table named Date that is based on the minimum and maximum values in the date field of your original table.
Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
- Then create another table called DatePeriod to define the specialized date period:
DatePeriod = UNION(ADDCOLUMNS(DATESMTD('Table'[Date]),"Type","MTD"),ADDCOLUMNS(DATESQTD('Table'[Date]),"Type","QTD"),ADDCOLUMNS(DATESYTD('Table'[Date]),"Type","YTD"),ADDCOLUMNS(DATESBETWEEN('Table'[Date],TODAY()-7,TODAY()),"Type","ThisWeek")) - In addition, we need to manage the relationship between them as shown below:
- In this way, we can use the type field in DatePeriod table as a slicer in order to filter all the visualizations in one slicer:
Note that I've changed a couple of the time data to 2023 in order to reflect the change:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Create a new table named Date that is based on the minimum and maximum values in the date field of your original table.
3 Replies
Hello Asmaa-elsheikh, and thank you for sharing a question with the Community. Please remember to adhere to the decorum of the Community Forum when asking a question.
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.Hi Asmaa-elsheikh,
Depending on your requirements, we recommend that you create two additional date tables and manage the relationship between them.
Please follow my steps:
- Create a new table named Date that is based on the minimum and maximum values in the date field of your original table.
Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
- Then create another table called DatePeriod to define the specialized date period:
DatePeriod = UNION(ADDCOLUMNS(DATESMTD('Table'[Date]),"Type","MTD"),ADDCOLUMNS(DATESQTD('Table'[Date]),"Type","QTD"),ADDCOLUMNS(DATESYTD('Table'[Date]),"Type","YTD"),ADDCOLUMNS(DATESBETWEEN('Table'[Date],TODAY()-7,TODAY()),"Type","ThisWeek")) - In addition, we need to manage the relationship between them as shown below:
- In this way, we can use the type field in DatePeriod table as a slicer in order to filter all the visualizations in one slicer:
Note that I've changed a couple of the time data to 2023 in order to reflect the change:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks so much for your support
- Create a new table named Date that is based on the minimum and maximum values in the date field of your original table.
