Forum Discussion

Peter7's avatar
Peter7
Frequent Visitor
5 years ago
Solved

Use slicer value in FILTER

Hey guys,

 

I am quite new in Power BI so I would be happy for any help you can give me on my problem - and let me know if you need more information. Here a short explanation:

 

I want to show the status of how many machines are done/pending per category at different positions in time. For that I have timestamps (FinishDate), which is blank if the machine is pending. For the value of how many machines are "done/pending" I've got measures which work fine and I already have a measure which gives me the values for a fixed point in time (f.e. 90 days past):

TotalMachinesDoneXDaysPast =
VAR ProgressX = CALCULATE ([TotalMachinesDone],
FILTER (Table1,Table1[FinishDate] >= TODAY()-90))
 
RETURN
[TotalMachinesDone]-ProgressX
 
I have a similar measure for the "pending" machines, except the "return" where i have my measure [TotalMachinesOpen]+ProgressX. That already works fine and I can create different visuals for these fixed points in time.  But I would like to have one visual and to choose per slicer how many days i want to "go" in the past - So basically I would just have to replace the "90" in the above DAX with the slicer value.. I already found the following question here in the community, where the problem is quite similar but i can't get that to work for mine:

https://community.powerbi.com/t5/Desktop/Use-selectedvalue-in-a-measure/td-p/483456

 

Here is where I am currently:

- I created a calculated table (DateDiffFIlter) with GENERATESERIES, which gives me de DateDiff (days) between all "FinishDates" and Today (increment 1) and there is no relation to my Table1 - I use these values in my slicer.

 

Then I have modified the above DAX to:

TotalMachinesDoneXDaysPast =
VAR CurrentSlicerValue = SELECTEDVALUE ( DateDiffFilter[Value] )
VAR ProgressX = CALCULATE ( [TotalMachinesDone],
FILTER ( Table1, Table1[FinishDate] >= TODAY()-CurrentSlicerValue ) )

RETURN
[TotalMachinesDone]-ProgressX
 
This doesn't work then - I get the status of today because the variable "CurrentSlicerValue" is blank. So how can I get the slicer value in the above DAX? Did i miss something with SELECTEDVALUE? 
 
As mentioned it already works for the status of today in general and if I manually choose the days in the past (-90), but I don't get it how i can make it work with per slicer. 
 
I hope my explanation is sufficient - let me know if I should provide more. Thank you in advance for any help!
Regards, 
Peter
Need Help
  • Peter7's avatar
    Peter7
    5 years ago

    I found the solution:

     

    include a "What If" parameter and change the GENERATESERIES of the thereby created table to the ones which I mentioned in the first question. 

     

    Afterwards I just had to change all the measures to the new table/column of the what-if and voilá: a single select slicer, that works as a time machine 😁

     

    Let me know if anyone else has the same problem and needs help - than I could provide more information about my solution.

2 Replies

  • Peter7's avatar
    Peter7
    Frequent Visitor
    5 years ago

    I found the solution:

     

    include a "What If" parameter and change the GENERATESERIES of the thereby created table to the ones which I mentioned in the first question. 

     

    Afterwards I just had to change all the measures to the new table/column of the what-if and voilá: a single select slicer, that works as a time machine 😁

     

    Let me know if anyone else has the same problem and needs help - than I could provide more information about my solution.

  • Peter7's avatar
    Peter7
    Frequent Visitor
    5 years ago

    Hey

     

    I just recognized that it already works if I use the slicer with checkboxes. Before i used it as a slider. 

    Well, I could do a workaround to give the user some possible points in time but is there any possibility to make it work as a slider as well?

     

    The problem seems to be, that the slider passes more than one value to the SELECTEDVALUE statement and therefore the alternate result (0) is chosen.

     

    Let me know if you have any ideas

    Best regards, 

    Peter