Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Dnzcn
Regular Visitor

Data Modeling

‎11-03-2022 02:05 AM

Hi everyone,

 

I have two historical tables and I am converting these tables to day by day with dimdate table using sql storedprocedure.  My aim is to report in PowerBI the leave information of the employees according to the work place on the specified date or date range using a date filter.

What I want to ask is that

-it takes a long time to convert tables day by day in sql. Is there any other way besides this method ? 

-How should a model be built in such a historical structure, is the day-to-day conversion method correct ?

(I wrote the record examples and the number of records in the tables before and after the conversion below. )

 

Employee Absence Table (885.000 records)

EMPOYEEIDABSANCE_CODESTARTDATEENDATE
66936032018-01-07 00:00:00.0002018-01-10 00:00:00.000
66936012018-02-01 00:00:00.0002018-02-05 00:00:00.000

 

Employee Assignment Records (87.000 records)

EMPOYEEIDDEPARTMAN_CODESTARTDATEENDDATE
669363CCEC22018-01-26 00:00:00.0002018-02-27 00:00:00.000
669363CCHA22018-01-01 00:00:00.0002018-01-25 00:00:00.000

 

Day by Day Workplace (34.000.000 records)

DATEDEPARTMAN_CODEEMPLOYEEID DATEDEPARTMAN_CODEEMPLOYEEID
1.01.20183CCHA266936 1.02.20183CCEC266936
2.01.20183CCHA266936 2.02.20183CCEC266936
3.01.20183CCHA266936 3.02.20183CCEC266936
4.01.20183CCHA266936 4.02.20183CCEC266936
5.01.20183CCHA266936 5.02.20183CCEC266936
6.01.20183CCHA266936 6.02.20183CCEC266936
7.01.20183CCHA266936 7.02.20183CCEC266936
8.01.20183CCHA266936 8.02.20183CCEC266936
9.01.20183CCHA266936 9.02.20183CCEC266936
10.01.20183CCHA266936 10.02.20183CCEC266936
11.01.20183CCHA266936 11.02.20183CCEC266936
12.01.20183CCHA266936 12.02.20183CCEC266936
13.01.20183CCHA266936 13.02.20183CCEC266936
14.01.20183CCHA266936 14.02.20183CCEC266936
15.01.20183CCHA266936 15.02.20183CCEC266936
16.01.20183CCHA266936 16.02.20183CCEC266936
17.01.20183CCHA266936 17.02.20183CCEC266936
18.01.20183CCHA266936 18.02.20183CCEC266936
19.01.20183CCHA266936 19.02.20183CCEC266936
20.01.20183CCHA266936 20.02.20183CCEC266936
21.01.20183CCHA266936 21.02.20183CCEC266936
22.01.20183CCHA266936 22.02.20183CCEC266936
23.01.20183CCHA266936 23.02.20183CCEC266936
24.01.20183CCHA266936 24.02.20183CCEC266936
25.01.20183CCHA266936 25.02.20183CCEC266936
26.01.20183CCEC266936 26.02.20183CCEC266936
27.01.20183CCEC266936 27.02.20183CCEC266936
28.01.20183CCEC266936 28.02.20183CCEC266936
29.01.20183CCEC266936    
30.01.20183CCEC266936    
31.01.20183CCEC266936    

 

 

Day By Day Absence Table (6.000.000 records)

DATEABSANCE_CODEEMPLOYEEID
7.01.20180366936
8.01.20180366936
9.01.20180366936
10.01.20180366936
1.02.20180166936
2.02.20180166936
3.02.20180166936
4.02.20180166936
5.02.20180166936
Labels:
Message 1 of 3
269 Views
0
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

‎11-03-2022 03:43 AM

@Dnzcn , You can follow table or measure way depending on need

 

Between Dates - Dates between
Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

 

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

Message 2 of 3
224 Views
2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

‎11-14-2022 12:14 AM

Hi @Dnzcn ,

 

Whether the advice given by @amitchandak  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


Looking forward to your feedback.


Best Regards,
Henry

Message 3 of 3
207 Views
0
amitchandak
Super User
Super User

‎11-03-2022 03:43 AM

@Dnzcn , You can follow table or measure way depending on need

 

Between Dates - Dates between
Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

 

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 2 of 3
225 Views

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All