Forum Discussion
Filter using What if parameter in a formula
Hi,
After my long time research and test, I finally figured out a method that uses a measure to replace the field [Units] which serves as the Value column of the pie chart:
Units Sum = var _table= FILTER('Pipeline data','Pipeline data'[Year enters pipeline]<='Select a year parameter'[Select a year parameter Value] &&'Pipeline data'[Year leaves pipeline]>'Select a year parameter'[Select a year parameter Value]) return SUMX(_table,[Units])Thank you very much!
The sample pbix file is attached below.
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi MargaritaG, many thanks for your suggestions. Unfortunately I still haven't found the magic formula to make this work.
SelectedValue only seems to be accepted in a formula when it is used with the data array in the Whatif parameter, e.g.
However, when the column formula is like this, nothing meets this critera, everything is No.
When I try to use SelectedValue with the slider value, the formula is not accepted.
I've tried wrapping the [Year enters pipeline] and [Year leaves pipeline] columns in SelectedValue, but again, everything is No.
The closest I can get to this working is without using SelectedValue, so:
This gives results, but it always uses the default value rather than the value on the slider. When I manually update the default value, the filter updates.
Is there a trick to getting this formula to updated with the slider?
The screenshot below shows this in the context of the dashboard. The slider is set to 2012, but the results are for 2020, which is the default for the Select a year value (shown in the formula bar at the top). You can see the results are for 2020 from the filters on the right. The column 2020/21 check hard-codes 2020 into the formula (
and has exactly the same number of results. This is confirmed by the screen shot below that shows the two filters with nothing ticked.
Apologies that this is all a bit messy, but hopefully it helps to explain what I am trying to achieve and where I am going wrong.
Thanks again
Jon
Hey,
I think it would be easier to understand if it works and what we are dealing with if you could add table visual of Item, [[Year Enters Pipeline], [Year Leaves Pipeline], [Measure with the logic]
Then it would be clear where it does not work.
Hi,
What I'm trying to create is a companion to this dashboard, but rather than focussing on what was granted each year I want to show what was still 'live' at the end of each year.
Each line of data only has one approval date, so for the approvals dashboard you can put the approval date in a slicer and all of the visuals will update when the slicer is changed.
For the pipeline, however, each line can be in multiple years (or none). I've created various objects in DAX to help me see if something is live at the end of the year; the formula to see what was live at the end of 2020/21 is:
2020/21 check = IF(AND([Year enters pipeline]<=2020,[Year leaves pipeline]>2020),"Yes","No"))
What I have been trying is to use a WhatIf parameter to replace the hard-coded dates in the formula above, then filter the report to only show the records that return "Yes" from this formula.
What Robert has shown above is that I can create a measure using the formula, and the data table (equivalent to the one on the third page of the approvals dashboard) will update as intended. However it doesn't seem to be compatible with the cards, cross-tabs and charts that are also included in the report. If I use the formula in a Column, which is what I had been trying (as it seems to be the only way to use it as a filter on all of the various visuals, across pages etc), it doesn't respond to the slider.
I'm trying to get to the point where I have a slicer, slider or whatever, that lets the user select the year. This needs to work in tandem with the filters by borough, type of supply etc and lead to the tables, cards and charts updating.
Apologies again that I am not explaining this very well, and many thanks for taking the time to read and respond to my posts!
Jon