Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi everyone,
I need to identify whether a student was on roll at random dates. I have their arrival and leaving dates in a table and figured I would simply substitute out null leaving dates and replace them with tommorows date, create a list of values between their date of arrival and the ammended leaving date and then expand. In the model I would link their unique ID with my student table and the date with my date table and then distinctcount the unique Id against a date.
let
TommorowsDate = Date.AddDays(DateTime.Date(DateTime.FixedLocalNow()),1),
Source = #"Student Database",
#"Added Adjusted Leaving Date" = Table.AddColumn(Source, "Adjusted Leaving Date", each if [Leaving date] = null then TommorowsDate else [Leaving date]),
#"Added On Roll List" = Table.AddColumn(#"Added Adjusted Leaving Date", "Date", each { Number.From([DOA])..Number.From([Adjusted Leaving Date]) }),
#"Expanded Date" = Table.ExpandListColumn(#"Added On Roll List", "Date"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"UPN-SUID", "Date"})
in
#"Removed Other Columns"
This works great except when I've looked at the vertipaq analyser it creates a table with the following charachteristics:
Row Labels | Cardinality | Table Size | Columns Total Size | Data Size | Dictionary Size | Columns Hierarchies Size | Encoding | User Hierarchies Size | Relationships Size | Table Size % | Database Size % | Segments # | Partitions # | Columns # |
On Roll | 36,252,386 | 73,920,216 | 73,856,880 | 71,440,792 | 2,162,760 | 253,328 | Many | 63,336 | 39.09 % | 35 | 1 | 3 | ||
Date | 8,855 | 71,752,280 | 71,277,320 | 404,080 | 70,880 | HASH | 97.15 % | 37.95 % | 35 | 1 | 1 | |||
RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61 | 120 | 0 | 120 | VALUE | 0.00 % | 0.00 % | 35 | 1 | 1 | |||||
UPN-SUID | 22,801 | 2,104,480 | 163,472 | 1,758,560 | 182,448 | HASH | 2.85 % | 1.11 % | 35 | 1 | 1 |
It seems that whilst this soloution works, I don't think 40% of my model is worth this and there must be a way to do this in dax. However, I fear its probably more complex than my current skillset so any steers in the right direction would be appreciated.
HI, @StanleyBlack
It is possible to be sovled when more corresponding data and the expected result is provided. I am glad to solve the problem for you.
Best Regards,
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Guys,
Data looks like this though I've annonomised the UPN-SUID column but it comprises a start date and end date. If the end date is null they are still present:
UPN-SUID,DOA,Leaving date
12600,01-Sep-14,09-Feb-17
12601,01-Sep-14,28-Jun-19
12602,01-Sep-14,
12603,01-Sep-14,11-Dec-18
12604,01-Sep-14,31-Oct-16
12605,01-Sep-14,15-Oct-17
12606,01-Sep-14,13-Sep-18
12607,01-Sep-14,11-Dec-18
12608,01-Sep-14,17-May-19
12609,01-Sep-14,10-Sep-17
12610,01-Sep-14,28-Jun-19
12611,01-Sep-14,
12612,01-Sep-14,
12613,01-Sep-14,
12614,01-Sep-14,
12615,01-Sep-14,
12616,01-Sep-14,28-Jun-19
12617,01-Sep-14,28-Jun-19
12618,01-Sep-14,28-Jun-19
12619,01-Sep-14,28-Jun-19
12620,01-Sep-14,28-Jun-19
12621,01-Sep-14,28-Jun-19
12622,01-Sep-14,28-Jun-19
12623,01-Sep-14,28-Jun-19
As per my soloution above I know I would need to replace the nulls with tommorows date to be able to do time intelligence on it. The reason I say tommorows date and not todays is that this table is loaded daily at 6am and so in the unlikely event that someone loads the output report between midnight and 6am the student would have left the previous day. That made sense to me but I'm not sure if it was patiularly clear.
I only need to return a count of students on roll at a paticular time so for example if I were to do a count of students on roll today from this data I would get 6 as they are null values and so have not left. I would want this to be able to answer that question at any given date.
The logic in my head is this: If the selecte date is between DoA and Leaving date then count as 1 else count as 0. This would enable me to do a trend line showing count of students over time.
Probably is a way but would need to see sample data and expected output.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |