Try the below:

Select A.* From Dimension A

Inner Join Fact B on A.ID = B.ID

where B.Date between A.Startdate and A.EndDate

One more method:

Select A.* From Dimension A

Where exists( Select 1 From Fact B on A.ID = B.ID

where B.Date between A.Startdate and A.EndDate)

Hi Royal,

According to your description, you want to filter the data base on the StartDate and EndDate, right? In this case we can use STRTOMEMBER function to achieve the requirement. Here is a sample query for AdventureWorks cube for your reference.

select
{[Measures].[Internet Sales Amount]
} on columns,
{[Date].[Date].members} on rows
from(
select
(
STRTOMEMBER("[Date].[Date].&["+@StartDate+"]"):STRTOMEMBER("[Date].[Date].&["+@EndDate+"]")
) on columns
from [Adventure Works]
) 

Reference:StrToMember (MDX)

Regards,