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.
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.
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 :
Is it a table that you have created in Power BI using the "New table" option?
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
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?
when you use the New Table function to create a query in PBID, the formula can not get any filters come from any slicers.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |