Union 2 Tables, but only apply slicer to 1

[Anonymous]

I have a requirement to filter a dataset with a date slicer but also show where date is null in the result set

e.g. 

TableA

Category, Date

A, 2019-08-01

A, 2019-08-03

A, 2019-08-05

A, null

B, 2019-08-01

B, null

If I have slicers: Category = A, Date between 2019-08-03 and 2019-08-05

The result I expect:

Category, Date

A, 2019-08-03

A, 2019-08-05

A, null

----------------------------------------

What I have tried:

I created another table that has same dataset (TableB) but with filter Date = null. I also have a Category dim table that i join to both TableA and TableB, and I have created a slicer attached to the Date field from TableA only.

and created a calculated table

CalcTable = union(TableA, TableB)

What I find is that the slicer that I apply on TableA is not updating records displayed in CalcTable.

Is there another way I could tackle this challenge or am I missing something with my calctable formula?

Thanks!

[v-shex-msft]

Hi @Anonymous ,

I'd like to suggest you to create a new date table(not has relationship to original table) as source of slicer.
After these steps, you can write a measure to compare current row contents and selected date range, return tag and apply on visual level filter to filter records.

Tag =
IF (
    MAX ( 'Table'[Date] ) IN ALLSELECTED ( 'Calendar'[Date] )
        || MAX ( 'Table'[Date] ) = BLANK (),
    "Y",
    "N"
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

I tried this method, but the filter doesn't seem to work exactly.

I am getting value = Y for some that are not in the date range selected.

Could you think of a reason why this could be happening?