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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors