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
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.
Solved! Go to Solution.
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
@ , 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |