Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a slicer or filter from a Measure

I am trying to create a slicer or filter for a measure

 

My source data only has a "Billing Start date" and no "Billing Finish Date" we only have the term of contract i.e. 12, 24, 36, etc

 

I have created a measure to create "Billing Finish Date"

 

Billing Finish Date = VAR vStartDate = MAX ( 'All Bookings'[Billing Start] ) VAR vTermMonths = MAX ( 'All Bookings'[Term] ) VAR vFinishDate = IF ( vTermMonths <> 1, EDATE ( vStartDate, vTermMonths - 1 ) ) VAR vResult = IF ( HASONEVALUE ( 'All Bookings'[Contract#] ), vFinishDate ) RETURN vResult
 
From this, I have created an "In Range" measure, which equals either "Expired" or "Booked"
 
In Range = IF('All Bookings'[Billing Finish Date]<=TODAY(),"Expired","Booked")
 
Now I need to Filter or add slicer against "Expired", "Booked"
 
Any help, advice would be appreciated 
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi Anonymous 

    Based on your explanation, I create a sample file, you can take it for reference. See sample file attached below.

    Besides, the easiest way is to create a column,

    In Range Col = IF('All Bookings'[Billing Finish Date]<=TODAY(),"Expired","Booked")

    Then, drag this column into the slicer:

    Result:

    Best Regards,

    Community Support Team _ Tang

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

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , You have to create an independent table(NewTable) with these two values

     

    Create measures using this

    countx(filter(values(Bookings[Contract#]), [In Range] =max(NewTable[Value])),Bookings[Contract#])

     

    Check my video on a similar topic

    Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for such a quick answer

       

      I have watched the video but as yet can’t get my head around what needs to be done, I believe I only need a small portion of your video and need two columns

       

      Contract # and In Range

       

      Is that correct?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      When I try to create the new measure suggested with the new table name I get lots of errors

       

       

      Any help will be appreciated

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      As I can't get this to work do I have any other options in being able to filter my data?

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi Anonymous 

    Based on your explanation, I create a sample file, you can take it for reference. See sample file attached below.

    Besides, the easiest way is to create a column,

    In Range Col = IF('All Bookings'[Billing Finish Date]<=TODAY(),"Expired","Booked")

    Then, drag this column into the slicer:

    Result:

    Best Regards,

    Community Support Team _ Tang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Works like a dream thank you 🙂