Forum Discussion

StanleyBlack's avatar
StanleyBlack
Helper I
6 years ago

Dax Alternative

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 LabelsCardinalityTable SizeColumns Total SizeData SizeDictionary SizeColumns Hierarchies SizeEncodingUser Hierarchies SizeRelationships SizeTable Size %Database Size %Segments #Partitions #Columns #
On Roll36,252,38673,920,21673,856,88071,440,7922,162,760253,328 Many 63,336 39.09 %3513
Date8,855 71,752,28071,277,320404,08070,880HASH  97.15 %37.95 %3511
RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61  1200120 VALUE  0.00 %0.00 %3511
UPN-SUID22,801 2,104,480163,4721,758,560182,448HASH  2.85 %1.11 %3511

 

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. 

 

Need Help

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      StanleyBlack
      Helper I
      6 years ago

      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.