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 to know which records were live ('in the pipeline') in the selected year. Using various dates I have calculated the year each record enters the pipeline and the year it leaves. The idea being to then filter the report using a column containing this formula:

 

Pipeline check = IF(AND([Year enters pipeline]<={Selected year],[Year leaves pipeline]>[Selected year]),"Yes","No")
 
I want the [Selected year] to be set by a slider on the dashboard, so I have a 'what if' parameter called 'Pipeline year', but the filter stops working when the parameter value is entered into the formula.
 
Dynamic check = IF(AND([Year enters pipeline]<='Pipeline year'[Pipeline year Value], [Year leaves pipeline]>'Pipeline year'[Pipeline year Value]),"Yes","No")
 
No matter what I try, I can't get the filter to work. The problem seems to be using the [Pipeline year Value] twice in the same formula as it works if I replace the second instance with a number, for exampe:
 
Dynamic check = IF(AND([Year enters pipeline]<='Pipeline year'[Pipeline year Value],[Year leaves pipeline]>2015),"Yes","No")
 
Any advice on how I can achieve the desired result would be very gratefully received!
 
Jon
 
  • 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.

10 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    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")

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    4 years ago

    Hi, 

    According to your description, I can roughly understand your requirement, I think the reason why your DAX formula can’t work is that you have to use the value of the measure [Parameter Value] in the What-if parameter you created, you can try my method to achieve this:

    This is the test data I created based on your description:

     

    Then I create a What-if parameter like this:

     

    Then create a measure like this:

    Pipeline check =

IF(MAX('Pipeline'[Year enters pipeline])<=[Parameter Value]&&MAX('Pipeline'[Year leaves pipeline])>[Parameter Value],"Yes","No")

    And you can create a table chart to place it like this and apply the visual filter like this to get what you want, like this:

     

    You can download my test pbix file below

     

    If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

    How to Get Your Question Answered Quickly 

    Thank you very much!

     

    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.

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    4 years ago

    Hi, 

    According to your description, I can roughly understand what you want to get. Would you like to post some sample data in table form or pbix file(without sensitive data) and your expected result(like the chart you want to get and the correct measure value based on your sample data)?

    Thanks very much!

    How to Get Your Question Answered Quickly 

     

    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.

    • jonbrooker's avatar
      jonbrooker
      Helper I
      4 years ago

      Hi, hopefully this file shows the issue.

       

      Example pbix file 

       

      Let me know if you need any more information or can't access the file.

       

      Thanks