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
JamesMarquezDev
Frequent Visitor

Variable Date from Measure Not Working in Filter Function

Why does hardcoding a date in the filter function result in a correctly filtered table:

 

 

Filtered Table = 
FILTER ( 
    table,
    table[date_column] >= DATE(2021,5,1)
)

 

 

 

However, using a variable date that was calculated does not work: 

 

 

Filtered Table = 
VAR StartDate = CALCULATE ( MIN ( 'calendar'[Date] ), ALLSELECTED ( 'calendar' ) )
RETURN
FILTER (
    table,
    table[date_column] > StartDate
)

 

 

 

Is this normal functionality of Power BI? My use case is that a user selects the date  range of a calendar table using a slicer. Then a filtered table is created using the slicer's minimum selected date to show rows that meet the expression.

5 REPLIES 5
m3tr01d
Continued Contributor
Continued Contributor

@JamesMarquezDev 
Your "Filtered_Table", is it a calculated table?

I'm using the FILTER function, but I did also try the CALCULATETABLE function which resulted in the same effect. The logic of my code and expression works fine when the date is hard coded. It just seems as though when using a variable to find a particular date, Power BI is running the FILTER function before assigning the date to the variable. In a normal programming project, I would use some print functions to identify what exactly is occuring while the function is running. Unfortunately I can't troubleshoot this problem in Power BI.

No, you didn't understand my question. 
This object :

m3tr01d_0-1627924788200.png

Is it a table that you have created in Power BI using the "New table" option?

m3tr01d_1-1627924840380.png

 

We will need more information about your calendar table (Whats the min and max range of this table). 
Also, if you can provide us a pbix example file, this would help us investigate

JamesMarquezDev
Frequent Visitor

I verified that your response is correct. To test it, I created a new Calendar table with a date range smaller than the original Calendar table, and it worked correctly. The new table was being filtered by the variable MIN date in the new test Calendar. If I want to allow my user to dynamically change the date range of the Calendar table without using a slicer, are there any other methods to allow a user to dynamically change the date range of the Calendar without using slicers that will still work correctly in DAX formulas with variables?

wdx223_Daniel
Super User
Super User

when you use the New Table function to create a query in PBID, the formula can not get any filters come from any slicers.

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.

Top Solution Authors