The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am very new to Power BI so I'm not sure if there is a simpler way to accomplish my use case.
I have multiple tables where an inspector will input data into at a particular date and time. These tables are in SQL Server.
I added two of these tables to the report as such:
What I want is that the "Date" columns in these tables be filtered by it's date. So I basically want all instances of '01/04/2022' to be shown regardless of time. I was trying to use the date slicer pictured, I created a table of dates in SQL Server and also used a direct query to include the table in Power BI:
The time for each day in this table is at midnight. I added the relationships but whenever I try to filter by the dates using the slicer the tables appear empty.
By the filter condition pictured I should be able to see all my data that is in April.
So how can I fix this? What am I doing wrong?
Solved! Go to Solution.
Hi @kvernon
Please open Power Query editor and change the Date format to Date only (not datetime) for both table
If you need to keep DateTime for your data table, please create another column for Date only and use this column for relationship
Find the attached pbix
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos
http://www.myaccountingtricks.com
Proud to be a Super User! | |
Try to create a column with just the Date without the time, and put this column in the slicer
It would have been nice if PowerBI just had a control where I can use to filter date and time normally but what I'll do is add a column with just date. Thank you
@kvernon
Please check the Datatype and Dateformat among Calender Table and your data table
DateTime or Date
DD/MM/YYYY or MM/DD/YYYY
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos
Proud to be a Super User! | |
Here is the properties view on the model for the calendar table:
And here in one of the data table:
I should note that the Data table would have date and times such as 01/04/2022 09:45:06 and all the dates in the Calendar table are at midnight. I don't know if that would have anything to do with it?
@kvernon
Can you please share pbix with non sensetive sample data (10 rows suffient)
Proud to be a Super User! | |
Ok here I made a sample report using Excel. U can download from this link:
Hi @kvernon
Please open Power Query editor and change the Date format to Date only (not datetime) for both table
If you need to keep DateTime for your data table, please create another column for Date only and use this column for relationship
Find the attached pbix
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos
http://www.myaccountingtricks.com
Proud to be a Super User! | |
So what I'll do is add a date column in the data table, the items in that table is added by a completely different application so I have to work thru that system which added some complexity but I managed to get it to work. I would like it if Power BI just had a control to handle filtering between dates so I didn't have to make my tables so cumbersome but it is what it is. Thank you for your help!