Forum Discussion

jonbrooker's avatar
jonbrooker
Helper 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.

Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago

Hi Jon:

 

You an try something along the lines of:

Amt by Selected Year =
VAR yr_comp = SELECTEDVALUE('Pipeline year'[Pipeline year Value])
RETURN
 IF(AND([Year enters pipeline]<=yr_comp, yr-comp>2015),"Yes","No")
I hope this helps!
)
jonbrooker's avatar
jonbrooker
Helper I
4 years ago

Hi, many thanks for your swift response!

 

I've tried updating my formula as follows:

 

Dynamic check = VAR yearcheck = SELECTEDVALUE('Pipeline year'[Pipeline year Value])
Return
IF(AND(Pipeline[Year enters pipeline]<=yearcheck,Pipeline[Year leaves pipeline]>yearcheck),"Yes","No")
 
However DAX says that Pipeline year Value may not be used in this expression. The expression works if I use [Pipeline year], ie the range of years in the Whatif parameter), but changing the slider has no effect.
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hey, 
    Perhaps you already did, but check datatypes, they should match for the two fields you're comparing.
    Also, instead of IF(AND(Pipeline[Year enters pipeline]<=yearcheck,Pipeline[Year leaves pipeline]>yearcheck),"Yes","No")
    write selectedvalue for both 
    -->
    IF(AND(SELECTEDVALUE(Pipeline[Year enters pipeline])<=yearcheck,SELECTEDVALUE(Pipeline[Year leaves pipeline])>yearcheck),"Yes","No")

    • jonbrooker's avatar
      jonbrooker
      Helper I
      4 years ago

      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 (

      2020/21 check = IF(AND([Year enters pipeline]<=2020,[Year leaves pipeline]>2020),"Yes","No"))

      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

      • 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.