Dax Alternative

[StanleyBlack]

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. 

[v-alq-msft]

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.

[StanleyBlack]

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.

[Greg_Deckler]

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