Show items with no data for date/time fields

[Anonymous]

Hi,

In a simple table I want to show all values of the 'ID' field even if there is no data. Therefor I am using the option 'Show items with no data'. Unfortunately it is not working as expected. In the table below the field 'ID' is coming from a dimension table. 'RUNTIME' and 'STARTTIME' are from the same Fact table. The dimension table and fact table have a direct relationship.

When I first add 'RUNTIME' and then 'STARTTIME' it is working as expected and showing ID 2.

However when I switch these two columns then I see duplicate records. How is this possible? How to avoid this?

When I switch the datatype for 'STARTTIME' from date/time to text then the duplicates are gone. However I prefer the more flexibel date/time format.

Thanks,

Rob

[v-henryk-mstf]

Hi @Anonymous ,

According to your question, you might as well try to change the table relationship to cross filter direction.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Llooking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-henryk-mstf ,

Unfortunately cross filter direction didn't work.

I am using DirectQuery on an Oracle database. Based on my tests it looks like DirectQuery cannot handle the combination of:

- 'Show items with no data'

- A date/time field from the fact table which is not at the latest column of the table

- Summarize of the date/time field is set to none

In the combination of these three situations I get duplicate records in my report. All actions below will not show duplicates:

- Changing summarize of the date/time field to for example earliest

- Changing the datatype of the date/time field to for example text

- Changing the order of the date/time field in the table

- Changing the setting 'Show items with no data'

I can live with "changing the datatype". However it is not my preference and I really would like to understand this behavior from Power BI.

Please let me know which information you need from me to be able to reproduce this incorrect/unexpected outcome.

Regards,

Rob

[Anonymous]

@PhilipTreacy @amitchandak 

I have not the rights to add a PBIX file to the topic. Unfortunaltely my company doesn't allow me to share DropBox or OneDrive documents with people outside the organization.

I still face this issue when combining:

- Direct Query

- 2 queries with a one-to-many relationship

- A date/time field (not in last column) with summarize 'None'

- Show items with no data: yes

I discovered that my duplicates will be gone if:

- I am using a Text field instead of a date/time field (not my preference)

- I change the summarize 'None' to Earliest or something else (not an option)

- I change the source from direct Query to Excel (not an option)

Any suggestions are very welcome.

Regards Rob

[PhilipTreacy]

Hi @Anonymous 

If your problem still exists please share your PBIX file.

Regards

Phil

[amitchandak]

@Anonymous , I doubt the default aggregation is changing. Right-click on the field in the visualization pane and check what is summarization in both cases

[Anonymous]

@PhilipTreacy @amitchandak 

In the visualization pane the aggregation is in both cases the same: 'Don't summarize'.

When changing it to 'Earliest' for the 'STARTTIME' the duplicates will be gone. However then I will loose data if there are multiple starttimes for one ID.

[amitchandak]

@Anonymous ,  I think you should work with datetime datatype.  As long as it not giving wrong answer