Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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 
1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

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:

v-xiaotang_0-1621421207601.png

Result:

v-xiaotang_1-1621421207606.png

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.

View solution in original post

6 REPLIES 6
v-xiaotang
Community Support
Community Support

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:

v-xiaotang_0-1621421207601.png

Result:

v-xiaotang_1-1621421207606.png

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
Not applicable

Works like a dream thank you 🙂

amitchandak
Super User
Super User

@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
Not applicable

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

Anonymous
Not applicable

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

 

Capture.JPG

 

Any help will be appreciated

Anonymous
Not applicable

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?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.