Calculate table does not work with fact table's date column

[MichaelSamiotis]

Hello everyone.

I have the below DAX formula which works perfectly but when I replace the 'Dim Dates'[Date] with 'Fact Sales'[Date] it only returns blank. I can't get why as I have a one-to-many between those two tables on the Date column.

Test =

VAR FilteredSales =

    DISTINCT (

        CALCULATETABLE (

            SELECTCOLUMNS (

                'Fact Sales',

                "Column 1", [Column 1],

                "Column 2", [Column 2],

                "Column 3", [Column 3]

            ),

            LEFT (

                'Fact Sales'[Code],

                1

            ) = "R",

            'Dim Dates'[Date]

                >= DATEVALUE ( "01/01/2020" ),

            'Dim Dates'[Date]

                <= DATEVALUE ( "31/12/2020" ),

            NOT (

                ISBLANK ( 'Fact Sales'[Column 1] )

            )

        )

    )

Thank you!

[MichaelSamiotis · Accepted solution]

Forgot to mention that I end this formula with:

RETURN
COUNTROWS(FilteredSales )