cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenBouck_
Frequent Visitor

Filter on date table won't show rows where date is blank

Hello,

 

I wish to filter data on date, to show data within a date range (which currently works) OR with no date (ISBLANK() returns True). As I'm using multiple data sources, I created a 'Dates' table as follows :

 

Dates = 
DISTINCT(
    UNION(
        SUMMARIZE('Table 1', 'Table 1'[Date comptable]),
        SUMMARIZE('Table 2', 'Table 1'[Date comptable]),
        SUMMARIZE('Table 3', 'Table 1'[Date comptable]),
        ...
    )
)

 

 with the following relations :

 

     Dates[Date comptable] (*) -> (*) 'Table 1'[Date comptable]

     Dates[Date comptable] (*) -> (*) 'Table 2'[Date comptable]

     Dates[Date comptable] (*) -> (*) 'Table 3'[Date comptable]

     ...

 

As excepted, the Dates[Date comptable] column contains one blank row, and all the dates from the dataset.

 

I'm using the following filter on the report :

BenBouck__0-1654870306482.png

And it shows no data /

BenBouck__1-1654870358813.png

 

However, when I use another table, for example 'Table 1', PBI sucessfuly filters the empty dates and counts the elements :

BenBouck__2-1654870481454.png

 

I tried to modify the relations from -> to <->, and it doesn't work. I also wanted to create one-to-many relations, as I use 'Distinct' on my Dates table, but the editor won't let me chose this option (it says the cardinality isn't valid for this relation).

 

Am I doing something wrong ?

 

Thanks in advance for helping me.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Do not derive your Dates table from the values in your fact tables.  You can use the min and max dates for the boundaries, but the Dates table itself must have a contiguous (ie no gaps) Date column.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Do not derive your Dates table from the values in your fact tables.  You can use the min and max dates for the boundaries, but the Dates table itself must have a contiguous (ie no gaps) Date column.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors