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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
visheshjain
Solution Supplier
Solution Supplier

Filter Slicer from Measure values

Hello eveyone,

I have 3 tables. Calendar, Sale and Parameter.

Parameter has values ranging from 1-24, which are the number on months I want to go back.

I am trying to filter a slicer which has dates from the calendar table, based on the month of the sales date.

I have taken the last sale date and then gotten the min and max dates in 2 different measures, using the parameter value.

Now I want to dynamically filter a slicer which has dates from the calendar table, with above mentioned min and max dates.

 

For e.g.

Last Sale Date is 17th May 2023.

Selected Parameter Value is 3.

Max Calendar Date will be 30th April 2023

Min Calendar Date will be 1st Feb 2023.

 

E.g. 2

Last Sale Date is 12th Feb 2023.

Selected Parameter Value is 5.

Max Calendar Date will be 31st Jan 2023.

Min Calendar Date will be 1st Sep 2022.

 

Now I want to filter the slicer value to have dates between 1st Feb 2023 and 30th April 2023.

This will be dynamic based on the parameter values.

 

I have tried using the relative date slicer, but that works based on the current date of the system.
I also cannot use the sale date in the relative date slicer either, as it is a fact table.

 

Please let me know if you need any further information.

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



17 REPLIES 17
johnt75
Super User
Super User

You need another measure like

Date is visible =
IF ( SELECTEDVALUE ( 'Date'[Date] ) IN CALENDAR ( [Min date], [Max date] ), 1 )

and use that as a visual level filter on the slicer.

Hi @johnt75,

 

The visual is giving an error.

Date is visible.png

P.S. 2 step and 1 step are my start and end dates.

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



what does it show if you click on "see details" ?

As you can see the start and end dates are not blank as shown in the card visual below.

visheshjain_0-1684327120329.png

 

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



try creating a temporary table like

tmp table =
ADDCOLUMNS ( VALUES ( 'Date'[date] ), "@step 1", [1 Step], "@step 2", [2 Step] )

and you can then use the data view to look for dates where either step 1 or step 2, or both, are blank.

Hi @johnt75,

 

The new table did not work as start date is 31-12-1899 and the end date is blank.

I think is cause of the parameters used in the measures, when using them in a table, there is no context.

visheshjain_0-1684392955310.png

 

I tried modifying your code to use DATESBETWEEN() instead of CALENDAR(), it is not giving an error, but its not working either.

 

Date is visible.png

 

Anything else that you can think of? 

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



You're right about the table lacking context. Try creating a table visual instead, use 'Date'[Date] and the two measures.

Hi @johnt75,

 

Still no luck!

 

visheshjain_0-1684403029938.png

 

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



That's the problem. You need to make sure that when step 1 and step 2 are calculated inside the date visible measure they get the same filters as are being applied in your card visuals.

Hi @johnt75,

 

Thank you for staying with me so far and responding instantly!

 

Okay. Done!

Solved the Step 1 and Step 2 problem.

 

Here is the current position:

visheshjain_0-1684405555566.png

 

I used the REMOVEFILTERS()/ALL() function and removed the filters from the Calendar Date column, so that only the last date of sales is considered.

And I used the above mentioned Date Visible measure on the slicer.

A weird thing was happeneing, that when I am using, DATESBETWEEN() function it works, not completely but it works, but when I use the calendar function to get the dates in between the start and end dates, it gives me an error, that the start date cannot be more than the end date.

 

visheshjain_1-1684405793544.png

 

 

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



I wonder if the problems are to do with it being the date table we're trying to filter. Instead of using IN you could try

Date is visible =
IF (
    SELECTEDVALUE ( 'Date'[Date] ) >= [2 Step]
        && SELECTEDVALUE ( 'Date'[Date] ) <= [1 Step],
    1,
    0
)

Hi @johnt75,

 

Tried and still not working.

 

Sharing a sample file for you to play around it if you will.

visheshjain_0-1684413636385.png

I tried this technique as well, but didn't seem to work. Have a look at it.

Create slicer with Custom Time Periods in PowerBI | Time Intelligence | MiTutorials - YouTube

 

Thank you!

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



Is Visible = 
VAR CurrentDate = MAX('Calendar'[Date])
VAR Step1 = [1 Step]
VAR Step2 = [2 Step]
RETURN IF( CurrentDate >= Step2 && CurrentDate <= Step1, 1, 0)

gives the correct values in the table, but it doesn't play nicely with the slicer. If I add it as a filter on the slicer then you can't select any dates at all. Not sure what's going on.

Show your data model/post a sample pbix. Most likely the calendar table needs to be disconnected.

Hi @lbendlin,

 

I have shared a sample file in message 13 of the thread. Sharing it again in this post.

 

If the calendar table is diconnected, then it will not be able to filter out data from the fact tables.

Here is a look at the data model. It is quite basic.

 

visheshjain_1-1684479778242.png

 

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



Your measure are a little too complex.  Use these instead:

 

1 Step = EOMONTH(CALCULATE ( MAX ( 'Sales'[Sale Date] ), ALL () ),-1)

2 Step = EOMONTH([1 Step], - [Parameter Value]  ) + 1

Hi @lbendlin,

 

Changed the measures but still, its not working with filtering the slicer.

 

Attaching my file again.

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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