Reply
BrentDC
Regular Visitor
Partially syndicated - Outbound

DATE filter problem in combined dashboard

Hi,

 

I have created a combined dashboard with a direct query source (Powerbi Dataset) and a DATE table with hierarchy created in Dax.

They are connected with a 1 to many cardinality.

 

When I want to use the DATE filter in the dashboard it does nothing. So the filter does not work, despite the connection and the matching dates.

 

Probably something in the settings is not right?

The latest version of BI desktop is installed.

 

The connectionThe connection

The connection

 

Without filteringWithout filtering

Without filtering

 

With filteringWith filtering

With filtering It doesn't show anything...

 

Thanx !

1 ACCEPTED SOLUTION
bcdobbs
Community Champion
Community Champion

Syndicated - Outbound

Hi @BrentDC ,

Do you have any access to the original dataset you're connecting to? 

My suspision is that it the Date_Order column actually contains times as well as dates eg 10/08/2021 11:55:31 for example. However in the model is set to format them all just as dates.

 

You can either create a pure date column in your remote dataset or:

 

If you install Tabular Editor (free version 2 is fine) and connect to your local model try doing:

 

1) Expand Relationships and click the relationship in question.

2) In the properties pane change the "Join On Date Behaviour" to "DatePartOnly"

bcdobbs_0-1645178597976.png

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

9 REPLIES 9
bcdobbs
Community Champion
Community Champion

Syndicated - Outbound

I'd check that the columns involved in the relationship are both set as Dates. It looks like when you filter your date table there are no matching values on the other end. This could happen if one was still text or if one was a date time not date.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Syndicated - Outbound

Hi,

They are both marked as Date/Time columns, I cannot modify the date_order column from the Direct query  It is marked from the source as date column.

 

bcdobbs
Community Champion
Community Champion

Syndicated - Outbound

Put the date fields from the two ends of the relationship into separate table visuals and have a look at their contents. I'm guessing one end has time parts that don't match.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Syndicated - Outbound

Hi, still the same problem.

2 tables content without filter 20212 tables content without filter 2021

2 separate lists with date (left direct query), right (Dax table).

 

with filter 2021with filter 2021

With selection only the right table shows the correct data. Left gets empty.

 

 

 

bcdobbs
Community Champion
Community Champion

Syndicated - Outbound

Can you create the following measure and drop it into a card visual:

 

RowTest1 =
CALCULATE (

    COUNTROWS ( Sales ),

    Sales[Date_Order] = DATE (2021, 08, 10)

)



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Syndicated - Outbound

Hi, it gives an empty calculation, really weird...

 

4.JPG

bcdobbs
Community Champion
Community Champion

Syndicated - Outbound

Hi @BrentDC ,

Do you have any access to the original dataset you're connecting to? 

My suspision is that it the Date_Order column actually contains times as well as dates eg 10/08/2021 11:55:31 for example. However in the model is set to format them all just as dates.

 

You can either create a pure date column in your remote dataset or:

 

If you install Tabular Editor (free version 2 is fine) and connect to your local model try doing:

 

1) Expand Relationships and click the relationship in question.

2) In the properties pane change the "Join On Date Behaviour" to "DatePartOnly"

bcdobbs_0-1645178597976.png

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Syndicated - Outbound

Hi,

 

Indeed, the original field type of date_order is datetime. I have to change the type in the original database structure. I will try this and let you know if it works.

 

 

Syndicated - Outbound

Hi, It isn't possible to change the orignal fieldtype.

 

But I've made a new column in the table 

A - DATUM = DATEVALUE(Sales[date_order])
 
So I can use this new column to link to the date table and it works.
So problem solved.
 
Many thanks!
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)