I have the Enterprise DNA Date table active in my Power BI app and it is working successfully throughout the application except for one particular data source that I just added yesterday.
I have the relationship setup with the new data and the Date Table exactly like I have with the other data sources but the Date/Filter that I have setup to filter by FY, FQTR and Month is not working on the new visual from the new data source.
I have checked the visual to ensure their are no other date filters working on the visual, and I know the data that I want to retrieve for certain dates is in the table as I can see it when I view the data in other queries and inside the Query Editor.
I can also add the Date filter from the Date table specifically to the Visual and then select the filtering and it works. It just doesn’t seem to work with the page Date slicer. All of the other visuals on the page work with the slicer.
I have also checked the interactions with the Page Filter/Slicer to ensure it was filtering on the new visual and it shows that it is, however, it doesn’t show any data. There is some random data that shows when I select, for example, the month of May in FQ3 but only a single row. if I select Month of June or July of same quarter nothing shows - even though I know the data is there.
Hi @dmartin, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.
A perfect initial question includes all of the following:
A clear explanation of the problem you are experiencing
A mockup of the results you want to achieve
Your current work-in-progress PBIX file
Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)
My data sources come from a Publicly traded Company and the data is sensitive so not sure if I can provide my PBIX file. What should I do in a case like that? Can someone Direct Message or email me to setup a one-on-one to take a look?
Per @Greg ‘s suggestion, we’ve created a number of free tools and processes you can use to create representative and/or masked datasets to post to the forum.
Hi @dmartin, did the response provided by users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
I’ve watched the videos on providing sample functions however, I don’t know that I can do that and reproduce the current results.
I am using a query to pull in the data from a SQL server and I have quite a few calculations within the query that create the source data.
The only difference that I can see in this query than with other data sets that are working is the I am using a MAX function within the Query but not sure why that would matter. When I look at the data table, the data contains rows that are within the date range that I am trying to filter - but nothing shows in the visual.
I am able to get a single row of data to display on the visual when I select a “May” date range. When I look at the data table and find the actual record that is displayed, the only thing that I can see that is different with that row and any other row is there is one column that contains a NULL value, however, when I eliminate that column from the data, the filter still does not work.
The only other thing that I have notice is that for the date relationship that I have setup, I am using a field that is called “created date”. I have a relationship setup between this 'created date" field and the ‘Date’ field in my Date table. The relationship is setup exactly like all of my other data sources that are working but the only difference is, that when I look at the “Created Date” field, it does not show a date table icon like the other date fields that I have relationships built for.
Could there be something that is not activated for that field to make Power BI know that it is a date field.
I have compared the format and settings with this date field and all the other date fields and they are identical.
If I use the “Create Date” field as a separate filter on the Visual that is not working with the page slicer and remove the page slicer filter from that particular visual, the Create Date filter works perfectly.
Also, while our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!
The solution ended up being quite simple. In the Date field that I was using from the table field, I had it formatted to “Date” from inside the Column Tools screen. However, when I looked at the date field from within the actual table data it still showed date and time.
Because I was using multiple tables to pull in my data, the date fields were not from the same tables. of the 5 different tables, 4 of them showed a date/time stamp of the date with time as 12:00:00 ( 01/10/2020 12:00:00), but the 5th table, the one that was not filtering, showed the date and time as well, but showed the actual time. This particular table was from a different server and it was recording an actual timestamp in the date field. for example (01/10/2020 03:15:32).
Because of this actual timestamp, when I tried to filter the data from this server, it was not showing because the Date Table was unable to match any of the times other than the ones that had 12:00:00. There were a few random matches on some dates but that was just coincidence that the timestamps for that data actually matched the 12:00:00 timestamp.
After insuring that all date fields, were formatted to only show “Date” the filter began working. You will want to do this from inside the Transform Data window.
For now, unless I actually need a timestamp, the first thing I do for all date fields in my model is to set the date fields to “Date” only. This will prevent this issue from happening again.