Strange Issue - My Date table is showing the same date for all rows in visuals - but still works

[minimalist]

I previously posted this in the Fabric section but think this is a better place for it. 

Notice how the Date field in this matrix is showing the same date on every row but this date does not match the Year and Month Name. Despite appearances, the Date field is filtering the Order table correctly. 

I only noticed this yesterday and I can't figure out what is going on. I don't use the 'Date' field in many visuals so don't know when this issue first occurred. 

My model has only one date table and it is marked as a date table. This visual above is only using a SUM() aggregation. The two other date fields shown are coming from the same Date table. 

This matrix shows what my Date table looks like. Everything looks as expected in the Lakehouse.

Any thoughts?

[minimalist · Accepted solution]

I just wanted to close this topic as three months later I solved it and it was surprisingly simple. 

We need dates in dd/mm/yyyy format and as they often switching to mm/dd/yyyy, we customise our date format in our PBI model. You'll notice the example date at the bottom of the pane below is the same erroneous date which appeared on every row of my visual. The underlying date was still correct but it was being explicitly formatted as 14/03/2019, not merely dd/mm/yyyy.

I changed the date format back to one of the standard options and the problem went away.

I still don't really know how this could happen. I would just urge caution when using custom date formats.

[v-dineshya]

Hi @minimalist ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Regards,
Dinesh

[minimalist]

Hi Dinesh

What do you need me to share and how do I share it?

Thanks

[v-dineshya]

Hi  @minimalist ,

Please place your sample data in excel file. In excel , choose the option as "Public" as shown in below snap. 

Please share the file through the below "browse' option, refer snap.

And please share the expected output screenshot, it will easy for me to replicate the scenario.

Regards,

Dinesh

[minimalist]

Hi Dinesh

Thank you for your offer to help. Unfortunately, I don't have a "browse" option. I tried to drag and drop two files but it failed.

I'll leave my notes here as I don't want to lose them. 

I created two tables, Orders and Dates. I trimmed business sensitive data from the Orders table so it is not private. I don't have senstivity labels in my Excel.

In my model, they are connected with a many to 1 relationship on Dates[Date] / Orders[Order_Date]

I suggest on the Orders table you create a measure:

Total_Orders = SUM( Orders [Total_Ex_VAT] )

Then create a tabular matrix with three fields from the Dates table (Year, Month Name and Date) and the Total_Orders measure just created.

What I would expect to see is a table like this screen grab but with the correct dates for each day. The values for the selected periods should be the same as shown below.

[v-dineshya]

Hi @minimalist ,

Please check the below things to fix the issue.

1. Even if your [Date] column is not being aggregated intentionally, Power BI sometimes automatically aggregates non-numeric columns, especially in certain visuals like Matrix or Card. This happens particularly if the field is not in the right area (not in "Rows" but in "Values").

Solution: Is the Date field placed in the Rows section of the matrix? If it is placed in the Values section by mistake, it would show something like MAX(Date) across all rows.

2. Sometimes when you are using Direct Lake in Fabric, Power BI visuals can get stuck showing an incorrect preview due to caching or internal metadata bugs, especially when schema or source has changed after visuals were built.

Solution: Try deleting the visual and recreating it from scratch. Use a Table visual instead of Matrix and check behavior.

3. Sometimes the model has two Date columns, e.g: a visible [Date] and a hidden calculated [Date], and the visual picks the wrong one.

Solution: In the Fields pane, hover over the [Date] field. Make sure there's only one instance of Date and it's from the correct Dates table. Check model schema for duplicate or hidden columns.

4. In some cases, if auto-date/time is turned on, Power BI might create internal tables and hierarchies that mess with behavior.

Solution: Go to File --> Options and settings --> Options --> Data Load. Make “Auto Date/Time for new files” is turned OFF. Avoid dragging [Date] directly, instead use explicit fields like [Year], [MonthName], or [Date] after confirming it's not hierarchical.

5. Fabric is still evolving, and the Direct Lake integration is not always predictable. Certain transformations especially dynamic date generation might behave inconsistently when used in visuals.

Solution: Test the same model in Power BI Desktop using Import mode. If it works there, it's likely a Direct Lake rendering bug.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.

Regards,

Dinesh

[minimalist]

Hi Dinesh

I have checked your proposed but unfortunately I didn't have any success.

Idea 1 -

The Date field is in the rows section with Date_Test in Values. I have created multiple visuals and the issues persists. 

Idea 2 - I recreated the visual many time and there is no change. I have tried table and matrix.

Idea 3 - There is only one date field. The Dates table was created in Power Query using M and is only refreshed on Jan 1st. 

Idea 4 - I am unable to test as I can't find the settings you refer to. File --> Options and settings --> Options --> Data Load does not seem possible on Fabric.

 Idea 5 - I will try it in Power BI desktop. While I want to resolve this issue, I have other work as a priority so I will have to try that later in the week.

Thank you for your suggestions.

[v-dineshya]

Hi @minimalist ,

Step 4 : Please check in Power BI Desktop . Go to File --> Options and settings --> Options --> Data Load. Make “Auto Date/Time for new files” is turned OFF.  Please refer snap.

Step 5:  As you mentioned in previous response, you need some time to test in Power BI desktop. Once testing is done in Power BI. Please let us know.

Regards,

Dinesh

[minimalist]

Hi Dinesh

I created a new semantic model in Power BI Desktop and imported the Dates and Orders tables directly from the Fabric Lakehouse. As you can see, both tables are showing real dates in the Date column, not 14/03/2019. As I am working with the same tables, not similar ones, I think this confirms that the tables are not the cause of the issue. 

The two measures are the same as before

Test_Date = MAX(Dates[Date])

Total_Orders = SUM(Orders[Total_Ex_VAT])

The relationship is the same 1 > Many

At the moment, I am able to continue using the Fabric model for my project but not being able to use the Dates[Date] field in a visual is a pain. Thankfully I can workaround this issue. 

[v-dineshya]

Hi @minimalist ,

If your issue is resolved , Please accept it as a solution. And, if you have any further query do let us know.

Regards,

Dinesh

[minimalist]

Hi Dinesh

The issue is not resolved and I don't see a resolution forthcoming.

To preserve my sanity and productivity, I am merely ignoring it for the moment 😊

Thank you.

Garreth

[v-dineshya]

Hi @minimalist ,

Thank you for the response, do you require any other assistance on this thread? If not we are closing this thread for now. If you have any further issue, please raise a new thread in the community forum, we are ready to assist you on it.

Thank you for being part of Microsoft Fabric Community. 

Regards,

Dinesh

[johnt75]

Do you have a custom or dynamic format string set for the date column? What does it look like in Data View, as opposed to in a table or matrix ?

[minimalist]

I built my date table using Fabric Dataflow Gen 2's Power Query editor. Here is a code snip which defines my start date with a dynamic end date.  I didn't write this code myself. I got it from a colleague.

I'm not using any date text formating or deriving the dates from other fields.

let
	StartDate = #date(2018,01,01) ,
	EndDate =  Date.EndOfYear(Date.AddYears( Date.From(DateTime.FixedLocalNow()),0)), 
	Source = List.Dates( StartDate,
			Duration.Days( EndDate- StartDate) +1, 
			#duration(1,0,0,0) ),
	#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
	#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
	#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})

Below is a snip from the Query editor showing some headers. To me it looks fine, and for the most part, it works fine too. I only started this model in February and this week is the first time I used the [Date] column from the Dates table in a visual. Normally I use [Year] or [Month Name] as my main date filters.

What is strange to me is that all the other columns in the Dates table filter Orders and other tables correctly. Even more strange how an incorrect date is visible when the underlying date is filtering correctly. 

[johnt75]

Try using Performance Analyzer to get the queries from both matrices, the one with Total Orders and the one with just columns from the date table, and run them in DAX Query view. See what output you get from that.

[minimalist]

I don't understand your first sentence. I don't know what Performance Analyser is. There is also no DAX query view in Fabric. 

Instead I queried the tables in the Lakehouse using SQL. I don't know if this achieves what you were thinking of. In case it matters, the Dates table is a proper date table with all dates between 01/01/2018 and 31/12/2025. 

Dates table - first 5 rows

Dates table - last 5 rows

Orders table - small selection

[johnt75]

Performance Analyzer and DAX Query View are tools in Power BI Desktop. You could download that and create a report connected to your model, or potentially use DAX Studio to connect to the model and run queries. Both will allow you to examine the data that exists in the date table inside the semantic model. You could run e.g.

EVALUATE
'Date'
ORDER BY 'Date'[Date]

and that would show all entries from the date table.

[minimalist]

I am using Power BI within Fabric which is different in some ways to  Power BI Desktop.

For example, there is no DAX query view. Also it is not possible to create calculated columns. 

I can create DAX measures and visualisations but querying the underlying data is done using Notebooks (python/spark/sql) or directly in the Lakehouse endpoint using SQL. 

EVALUATE
'Date'
ORDER BY 'Date'[Date]

is similar to 

SELECT 'Date'
FROM [lakehouse].[dbo].[Dates]
ORDER BY 'Date';

I think there is a bug in the tool which is causing a date to appear in the visualisation. 

I added the following measure to my model...

Date_Test = MAX(Dates[Date])

.... and created another table visualisation with only the Date column from the Dates table and the new measure. I just don't understand how this is happening. It's kinda funny (but also frustrating) .

I think I will try to recreate the issue in Power BI desktop which I don't use much anymore.

[wardy912]

Hi @minimalist 

It looks like there isn't an active relationship between the date and orders table

[minimalist]

Sorry, I incorrectly marked your response as a solution. Fixed now

There is a relationship between the orders and dates tables (Many to 1).

For testing I deactivated all other relationships. The relationship seems to be working correctly and the values shown in the table above are correct for the filter context applied by the Dates table. It is only the Date field that is displaying an incorrect date. 

When I manually checked the source data for Sept, Oct, and Nov 2022, the order values match those shown in the table..