Forum Discussion
Date Table Slicer Not Working
Hello,
I have spent too much time on this and I can't seem to find the solution, I have a report with date table and some other table (agent_transactions) where it has transaction info and TransactionTime. I want to filter the TransactionTime based on the Date table but it is not working.
- I have a relationship between Date table and Transactions table
- both columns in the relationship have Date data type
You can view my .pbix file (download from my google drive) , i would really appreciate your help here, i need to change the slicer and agent_transactions table should react but it isnt the case here..
this is the link in case the hyperlink is not working (https://drive.google.com/file/d/1cws2eojwwOitjx7SWnm85XoDorEjjn7p/view?usp=sharing)
Anonymous as others suggested, the transaction time column contains the time element with it, to test, I added a new column using DAX and called it Transaction Date and have a relationship with the Date dimension and everything is working as expected. Check the attached file.
👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️
5 Replies
I suggest you focus on the Agent Transaction table and the Transaction Time column. Since I don't have access to your data source I cannot determine what Power Query thinks of your data. But I can tell you this: When you look at the data in the Data View and expand a column, it will show the the list of DISTINCT values. For example, if you expand Day under Dim Date you get 7 DISTINCT days of the week.
But look at the below screen shot. I have expanded the Transaction Date column and see SEVERAL rows all indicating "1/1/2023", not ONE DISTINCT row as I expected. And if I select one of the values, I get ONE row filtered. This tells me that maybe that column is actually a Date/Time column masquerading as a Date. I don't care that the Column Tools menu option in Power BI says that it is a Date. Check your Data Types in Power Query. Also, the fact that the column NAME is "Transaciton TIME" is suspecious!
Hello Anonymous ,
I've spent sometime looking into your report and trying to figure out what's wrong, also I created a new dim date table using calendar auto and connected it with your transaction table and yet its giving the same issue.
I agree with ToddChitt , that there's something suspicious in the transaction time column but we can check the transaction table in Power query since we don't have access to the db. so I sugest to look into the transaction time column in Power query because it seems that the problem is from there.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Anonymous as others suggested, the transaction time column contains the time element with it, to test, I added a new column using DAX and called it Transaction Date and have a relationship with the Date dimension and everything is working as expected. Check the attached file.
👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️
Thank you,
TransactionTime originally did contain date and time element as well. I changed it's format to date only but still the slicer didn't work. I have created a calculated column which will have the value of the date part only from my TransactionTime column and it is working.
I wonder why changing the format of TransactionTime format to Date did not solve the issue!
Anonymous when you change the format of a column, it just changes the display value of that column not the internal data, so changing your date/time column to date format doesn't mean the time value is removed from that column. I hope it answered your question.
