Forum Discussion
Table is not filtering on Dates
Hi Power BI community,
I have created a manual date table using the DAX formula:
Dates =
ADDCOLUMNS(
CALENDARAUTO(),
"Year",YEAR([Date]),
"Quarter No", QUARTER([Date]),
"Quarter", "Q"&QUARTER([Date]),
"Month No",MONTH([Date]),
"Month",FORMAT([Date], "MMM"),
"Day",DAY([Date]),
"Date_Full",DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))
)
I have imported a table from SharePoint were other dates are present.
I want to filter the SharePoint list based on the date selected in the custom table, but I am not having any result:
Can someone please help me,
Any help will be much appreciated.
Hi JB0007 ,
Firstly, you could change the second table to slicer visual.
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers
Secondly, the [start date] in the first table is datetime type so it will not It is not equal to the date selected in the second table -- 8:00 am <> 12:00 am. I suggest you to change them to date type for comparison.
At last, you could create a measure to rank the [start date]. Set the selected date as 0 and sort dates order by measure.
_start = SELECTEDVALUE(Table1[start date]) rank_ = IF(SELECTEDVALUE(Table1[start date])=SELECTEDVALUE(Table2[date]),0,RANKX(all(Table1),[_start],,ASC))Best Regards,
Jay
3 Replies
- amitchandak
Super User
@ , Not able to join dots. Can you explain what is needed and the logic behind that?
see if this can help
Hi amitchandak , thank you for your feedback.
I have removed the EndDate column because it is irrelevant.
But lets say, I want to filter the first table based on the date selected from the second table.
I can use the dates from the same table (first table) but I cannot customize the date as I wanted e.x.
I want to set Today as the first date in the table and then sort by in descending order.I can only achieve this using a Custom Date Table (which is the second table).
However, when a select for example 25 Feb, it is not filtering the table.Hi JB0007 ,
Firstly, you could change the second table to slicer visual.
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers
Secondly, the [start date] in the first table is datetime type so it will not It is not equal to the date selected in the second table -- 8:00 am <> 12:00 am. I suggest you to change them to date type for comparison.
At last, you could create a measure to rank the [start date]. Set the selected date as 0 and sort dates order by measure.
_start = SELECTEDVALUE(Table1[start date]) rank_ = IF(SELECTEDVALUE(Table1[start date])=SELECTEDVALUE(Table2[date]),0,RANKX(all(Table1),[_start],,ASC))Best Regards,
Jay
