Forum Discussion

jonbrooker's avatar
jonbrooker
Icon for Helper I rankHelper I
4 years ago
Solved

Filter using What if parameter in a formula

Hi, I'm trying to find a way of filtering a report based on a simple calculation using the value set by the user on the dashboard, but I just can't find any way to make it work.   Basically I want ...
  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

jonbrooker's avatar
jonbrooker
Icon for Helper I rankHelper I
4 years ago

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