Forum Discussion
Combining FILTER and SWITCH for dates
Hi - I've built a table for my calendar, which includes columns to identify Today, Yesterda, Week to Date etc.
| Date | isToday | isYesterday | isWeekToDate |
| 20/07/2024 | 0 | 0 | 0 |
| 21/07/2024 | 0 | 0 | 0 |
| 22/07/2024 | 0 | 0 | 1 |
| 23/07/2024 | 0 | 0 | 1 |
| 24/07/2024 | 0 | 1 | 1 |
| 25/07/2024 | 1 | 0 | 1 |
| 26/07/2024 | 0 | 0 | 0 |
What I'm trying to do is allow the user to select from a static table, which corresponds to those columns, and have Power BI create a table of those dates, which I can then use in the model.
The sort of thing I've tried looks like this:
FilterDates =
switch(
TRUE(),
SELECTEDVALUE('Date Options'[Date Option] = "Today"),filter('reporting dimCalendarTVS','reporting dimCalendarTVS'[isToday]=1),
SELECTEDVALUE('Date Options'[Date Option] = "Yesterday"),filter('reporting dimCalendarTVS','reporting dimCalendarTVS'[isYesterday]=1)
)
When I try that I get an error message saying
A single value for column 'Date Option' in table 'Date Options' cannot be determined. This can happen when a measure formula
refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Am I doing something fundamentally wrong? Has anyone tried something similar? It feels like it should work, but nothing that I've tried has done the trick. The filter statement on its own works fine - it's when I try to combine it with a switch statement that it falls over.
This is my first post, so please be gentle. If you need any more info about table etc. just let me know.
Had a further think about this. There might be a solution using field parameters.
If you create a field parameter for each of the date options (these will go in a slicer) and then write individual measures for these options, for example,
MeasureToday = CALCULATE(SUM(TableFact[Amount]), TableDates[isToday] = 1)You can write a measure that uses SWITCH to call the correct measure based on slicer selection (which i think you were attempting in the original post).
There are some complications with getting the SELECTEDValue as per
https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/
but there are solutions in the comments below the article.
4 Replies
- HotChilli
Community Champion
"What I'm trying to do is allow the user to select from a static table, which corresponds to those columns, and have Power BI create a table of those dates, which I can then use in the model." - this is not going to work.
What is it you are trying to use this for?
What I'm trying to do is create a slicer (based on the static table) which the user can use to select the time period for the report. So the report might be, for example, sales value, and the user can decide if they want to run it for Today, Yesterday, Last Week, Last Month etc. My thinking is that I could link the date from the sales table to the generated dates to make the report dynamic based on the user's selection.
- HotChilli
Yes that sounds possible. I think (from seeing an example of this type of thing a long time ago) that you use the
Date-options table to filter the dates table and the Date-options table contains dates in a column to correspond to today / last week etc. It's a static table that updates at refresh time.
Can't remember the details but search blogs/youtube and hopefully you can find an example.
- HotChilli
Had a further think about this. There might be a solution using field parameters.
If you create a field parameter for each of the date options (these will go in a slicer) and then write individual measures for these options, for example,
MeasureToday = CALCULATE(SUM(TableFact[Amount]), TableDates[isToday] = 1)You can write a measure that uses SWITCH to call the correct measure based on slicer selection (which i think you were attempting in the original post).
There are some complications with getting the SELECTEDValue as per
https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/
but there are solutions in the comments below the article.
