Forum Discussion

mob99pk's avatar
mob99pk
New Member
4 years ago

How to use userselected Measure inside Filter() function

Hi Dear Comunity,

I have simple data set that has task name , Start date and end date. 

 

I have to create a gannt chart like below but the requirement is that user should click the on bar and he should get all the tasks starting 30 days before the selected task , and 30 days after the selected task.

 

 

 

Now my solution,

I have created two measures  start and end date based on task selection like this,

 

Selected_Task_End_Date =
(CALCULATE(
min( Sheet1[end date] ),
FILTER(Sheet1,Sheet1[Task]= SELECTEDVALUE(Sheet1[Task]))
))+30
 
Selected_Task_Start_Date =
(CALCULATE(
min( Sheet1[start date] ),
FILTER(Sheet1,Sheet1[Task]= SELECTEDVALUE(Sheet1[Task]))
))-30
 
 and then i am creating another table which should filter the value on the fly and make a filtered table where start date is greater than Selected task start date and less than selected task end date.
 
Ganntt_table = CALCULATETABLE(Sheet1, filter(sheet1, Sheet1[start date] >= Sheet1[Selected_Task_Start_Date] )
 
But this doesnot filter any thing,
on the other hand , if i give a staitic input to filter it works,
Ganntt_table = CALCULATETABLE(Sheet1, filter(sheet1, Sheet1[start date] >= Today() )
 
 
Can some one help me how to use this user selected measure inside the filter funciton 
 
 
Many thanks

 

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Using SELECTEDVALUE inside CALCULATE is asking for trouble if you use the same table or related tables..  Are you familiar with the concepts of row context and filter context, and with disconnected tables?

     

    A safer way would be to use variables, like so:

    Selected_Task_End_Date =
var t = SELECTEDVALUE(Sheet1[Task])
return CALCULATE(
min( Sheet1[end date] ),
Sheet1[Task]= t
)+30
  • mob99pk's avatar
    mob99pk
    New Member
    4 years ago

    hi Dear ibendlin,

    Many Many thanks for replying , 

     

    I did apply the solution as you mentioned,

    Selected_Task_End_Date

    but this is still not filtering 

     

    Any 

    Ganntt_table = CALCULATETABLE(Sheet1, filter(sheet1, Sheet1[start date] >= Sheet1[Selected_Task_Start_Date] )

     

    Any guidance to fix that ?

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      You need to use the same approach again. Move the measure out of the filter condition into a variable before the CALCULATE.

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

    Hi, mob99pk 

    May I ask if your problem has been solved? Is the above post helpful to you?

    If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you

     

    It makes it easier to give you a solution.

    1. Sample (dummy dataset) data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

     

    Best Regards,
    Community Support Team _ Zeon Zheng

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • mob99pk's avatar
      mob99pk
      New Member
      4 years ago

      hi ,

      Team .

       

      Thanks alot for your support but this didnt support, i still cant filter it like this.