DAX - SCD logic

[singupil]

Hi Friends,

I had one scenario in SSAS Tabular Model, where i have 2 objects.

1) SourceData Object - which is a SCD 2 Object where we track all the history for the changes
2) Dim Date Object

I need help in writing a DAX expression where the user selects the date from Dim Date object based on that we need to populate the latest records from the SourceData Object (ie.,. the date should lie between start date and end date)

Sample SQL Code for your reference.

Create table #sourcedata
(
Source_sk int identity(1,1),
id int not null,
name varchar(500) null,
city varchar(500),
startdate date,
enddate date
)

insert into #sourcedata values(1,'santosh','US','2021-01-20','2021-05-28')
insert into #sourcedata values(1,'santosh','Canada','2021-05-29','2021-07-20')
insert into #sourcedata values(1,'santosh','London','2021-07-21','9999-01-01')
insert into #sourcedata values(2,'harish','US','2021-02-26','2021-07-12')
insert into #sourcedata values(2,'harish','france','2021-07-13','9999-01-01')
insert into #sourcedata values(3,'george','newzealand','2021-06-13','9999-01-01')
insert into #sourcedata values(4,'flintoff','australia','2021-09-13','9999-01-01')

GO
create table #DimDate
(
date_sk int not null,
Date date not null
)

insert into #DimDate values (1,'2021-06-10')
insert into #DimDate values (2,'2021-07-15')
insert into #DimDate values (3,'2021-09-18')

Desired Results in the Cube:
Case 1 : if user selects date as 2021-06-10 then below records need to be populated in cube.
id name city startdate enddate
1 santosh Canada 2021-05-29 2021-07-20
2 harish US 2021-02-26 2021-07-12

Case 2 : if user selects date as 2021-07-15 then below records need to be populated in cube.
id name city startdate enddate
1 santosh Canada 2021-05-29 2021-07-20
2 harish france 2021-07-13 9999-01-01
3 george newzealand 2021-06-13 9999-01-01

Case 3 : if user selects date as 2021-09-18 then below records need to be populated in cube.
id name city startdate enddate
1 santosh London 2021-07-21 9999-01-01
2 harish france 2021-07-13 9999-01-01
3 george newzealand 2021-06-13 9999-01-01
4 flintoff australia 2021-09-13 9999-01-01

Thanks in advance
Santosh S

@amitchandak @VahidDM 

[v-yanjiang-msft · Accepted solution]

Hi @singupil ,

According to your description, here's my solution.

1.There's no relationship of the two tables.

2.Create a measure in SourceData table.

Check =
IF (
    SELECTEDVALUE ( DimDate[Date ] ) >= MAX ( 'SourceData'[Start Date] )
        && SELECTEDVALUE ( DimDate[Date ] ) < MAX ( 'SourceData'[End Date] ),
    1,
    0
)

3.Put the measure to the visual filter and select 1.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[smpa01]

@singupil  what is the logic behind desired result?