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

Get 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

Reply
StanleyBlack
Helper I
Helper I

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. 

 

3 REPLIES 3
v-alq-msft
Community Support
Community Support

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.

Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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