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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CobraKAI
Frequent Visitor

Slowly changing dimension data included into report and measure

Hello,

I have a DataWarehouse (DW) with slowly-changing dimensions and Temporal tables, with ValidFrom and ValidTo fields.

(https://radacad.com/temporal-tables-a-new-method-for-slowly-changing-dimension
In PBI I have used Power Query Editor to extract data from said DW. 
I have some tables that is built by Power Query.


What I want is to now also have the historical data (Temporal tables) in table 'MembershipTemporal' in order to track membership trend (among other things) and also update the measures in the report with validfrom <= min(date) && validto >=min(date) .

 

Im thinking about using Power Query to get all data from 'MembershipTemporal' table and create a new table from that, and then use Merge to other tables (that is created by Power Query as well)  that is using the same fields from the 'Membership' table that has current data.


Is there a better ,smarter way?

Im stuck with using Power Query and rewriting the measures as it is an already older PBI report that is being updated extensively.

1 ACCEPTED SOLUTION

Update:
I went with a simple solution of just using UNION in my SQL code for joining all current data table with the history data table. Then I used the DAX code above in my measures

View solution in original post

3 REPLIES 3
VasTg
Memorable Member
Memorable Member

@CobraKAI 

 

I would recommend to bring in the SCD2 table as it is and expand the date range as shown in the post for a scalable approach and keep a column that flags current.

 

https://community.powerbi.com/t5/Desktop/Transform-StartDate-and-FinishDate-to-table-with-all-dates-...

 

 

You could filter out the history(rows) based on your requirement. 

 

 

If it helps, mark it as a solution

Kudos are nice too

Connect on LinkedIn

But would flags help to look at trends over distinct counts over time? E.g. one line chart visual is membership count over time. 
For that I need current and history data of members (hence tables Membership and MembershipTemporal) and also that DAX code I mentioned above in order to filter on dates and get the line chart for selected period. No?

Update:
I went with a simple solution of just using UNION in my SQL code for joining all current data table with the history data table. Then I used the DAX code above in my measures

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors