Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
singupil
New Member

DAX - SCD logic

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 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @singupil ,

According to your description, here's my solution.

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

vkalyjmsft_0-1638526666150.png

 

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.

vkalyjmsft_1-1638526768740.png

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.

View solution in original post

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @singupil ,

According to your description, here's my solution.

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

vkalyjmsft_0-1638526666150.png

 

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.

vkalyjmsft_1-1638526768740.png

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
Super User
Super User

@singupil  what is the logic behind desired result?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.