Numbers in Matrix Table become blank after adding slicer selected value as column

[Anonymous]

Just started using PowerBI for a short time. I am adding a constant date column refelecting slicer selected value into Matrix table. It serves as a bench mark date for calculating as-of overdue date.

For example when I choose 1 Jan and 30 Dec in slicer, this column should display these 2 dates in all rows respectively. Then I can further add a dynamic "Overdue day" column = "Target Completion Date" - this clicer column. And this "Overdue day" value should be changing when selected date in slicer changed.

However all figures become blank when slicer is updated. Really can't figure out what the reason is. 

It's strange that some rows still showing the figures but those "Start date" in FACT table doesn't exist in date table. If it's because of zero value, all figures should be blank when I choose a very big date like 2030. 

The date table in attached file is only for this slicer as users should only pick dates in coming 10 years. That's why it won't be assocated with my FACT table.

Link for PBIX file

[Anonymous · Accepted solution]

Update: 

Just filter out empty number columns and the result is what I expected. So it's a solution for me.

Thanks so much for comment above. As this constant column only change with slicer so I should not assign relationship for it.

[Anonymous]

Hi @Anonymous ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

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

[Anonymous]

Hi Alxis,

Thanks for your prompt reply.

However my scenario is let user choose a future date (coming 10 years) which will be beyond most of "Start date" and some of "Completion date" in FACT table. And also this picked up date has nothing to do with any date in FACT table actually. I am not sure if such relationship is appropriate. 

Just need to display this slicer date in every row and use it to calculate a as-of overdue date accordingly. Of course I will try also your solution first. Thanks!

[AlexisOlson]

There are a couple of issues here. Your date table should contain at least all of the dates in your fact table and you probably want a relationship from that table to your fact table.

I'd suggest defining your date table like this:

Date_Checking =
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( MIN ( FACT_Ageing[Start Date] ) ), 1, 1 ),
        DATE ( YEAR ( MAX ( FACT_Ageing[Target Completion Date] ) ), 12, 31 )
    ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Day", DAY ( [Date] )
)

and then creating a relationship

You'll still get empty rows most of the time since [Date_Report] is defined independently of the fact table.