The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I want to filter a table in periods of five years (basically, create a slicer that lets the user choose to see data from the last 5, 10, 15, etc. years).
To accomplish this, I believe the best idea is to create a measure that checks each cell in the date column and determines if it is within the selected period, returning a 1 if true and 0 if not. Then, I want to filter the table using this new column.
The only issue is that I can't seem to find a way to do this.
the code would look something like this
filter =
VAR _period = [period]
RETURN
IF('TableYear'[Date] >= _period, 1, 0)
basicly take a table like this
price | date |
54 | 01/01/2020 |
34 | 01/01/2017 |
756 | 01/01/2016 |
54 | 01/01/2014 |
53 | 01/01/2012 |
and if the slicer is the (past 5 years) return this
Price | Date | T/F |
54 | 01/01/2020 | 1 |
34 | 01/01/2017 | 1 |
756 | 01/01/2016 | 1 |
54 | 01/01/2014 | 0 |
53 | 01/01/2012 | 0 |
Wish there was a way to use a measure in an advanced filter, but unless it's a Top N filter, there is no option to do so.
Solved! Go to Solution.
You can use a measure as a visual filter. It has to be numeric though, not True/False.
Your date range selection must be fed from a disconnected table. Then probe its value(s) with your measure and return 1 or 0. Add the measure to the visual filters, and set it to "is 1".
Hi @Kuma64 ,
I hope this information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.
Thank you!!
Hi @Kuma64 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you!!
Hi @Kuma64 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you @lbendlin for the prompt repsonse.
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you!!
You can use a measure as a visual filter. It has to be numeric though, not True/False.
Your date range selection must be fed from a disconnected table. Then probe its value(s) with your measure and return 1 or 0. Add the measure to the visual filters, and set it to "is 1".