Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Dax Experts,
I have assignmentstartdate , assignementenddate and candidate no in my data.
I need to create 2 calculated columns. If next assignment of candiate is starting in less then =4 days then it should be in one group or else new group should be started.
Required columns are marked in bold.
AssignmentStartDate | AssignmentEndDate | CandidateNo | GroupStartDate | GroupEndDate |
06-04-2010 | 06-10-2010 | 123 | 06-04-2020 | 27-02-2011 |
07-10-2010 | 12-11-2010 | 123 | 06-04-2020 | 27-02-2011 |
13-11-2010 | 27-02-2011 | 123 | 06-04-2020 | 27-02-2011 |
25-07-2011 | 31-12-2011 | 123 | 25-07-2011 | 15-04-2012 |
03-01-2012 | 15-04-2012 | 123 | 25-07-2011 | 15-04-2012 |
Request you to please help here.
Thanks!
Solved! Go to Solution.
Hi @Anonymous ,
First, please add an Index column in Power Query Editor.
And then, try to create calculated columns like so:
Is less than or eq 4 =
VAR PreviousIndex = [Index] - 1
VAR ThisStartDate = [AssignmentStartDate]
VAR PreviuosEndDate =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
FILTER ( 'Table', 'Table'[Index] = PreviousIndex )
)
VAR DateDiff_ =
DATEDIFF ( PreviuosEndDate, ThisStartDate, DAY ) + 0
RETURN
IF ( DateDiff_ <= 4, 1, 0 )
Count 0 Value =
CALCULATE (
COUNT ( 'Table'[Is less than or eq 4] ),
FILTER (
'Table',
'Table'[Is less than or eq 4] = 0
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
)
GroupStartDate Column =
CALCULATE (
MIN ( 'Table'[AssignmentStartDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[Count 0 Value] = EARLIER ( 'Table'[Count 0 Value] )
)
)
GroupEndDate Column =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] >= EARLIER ( 'Table'[Index] )
&& 'Table'[Count 0 Value] = EARLIER ( 'Table'[Count 0 Value] )
)
)
For more details, please check the attached .pbix file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I've come up with this to eliminate the context transition
ColumnTest =
VAR _CandidateNo = TableA[CandidateNo]
VAR _index = TableA[Index]
RETURN
COUNTROWS(
FILTER (TableA,
TableA[CandidateNo] = _CandidateNo &&
TableA[Is less than or eq 4] = 0 &&
TableA[Index] <= _index)
)
I've not tested it for performance so see how you get on.
Further performance tips :
1. create the columns in Power Query instead.
2. Use measures instead of calculated columns
3. Switch off 'Auto date/time' in Options
4. Make sure date fields are Date type (not date/time - if you don't need it)
5. Reduce the columns in the Fact table (There are not many in the provided sample data but maybe the real data has more)
Hello All,
Thanks for all your help!
I have created GroupStartDate and GroupEndDate in very easy way using allexcept and it working as expected.
Please find below all the calculated columns:
Is less than or eq 4 =
VAR PreviousIndex = [Index] - 1
VAR ThisStartDate = [AssignmentStartDate]
VAR PreviuosEndDate =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
FILTER ( 'Table', 'Table'[Index] = PreviousIndex )
)
VAR DateDiff_ =
DATEDIFF ( PreviuosEndDate, ThisStartDate, DAY ) + 0
RETURN
IF ( DateDiff_ <= 4, 1, 0 )
Count 0 Value =
VAR _CandidateNo ='Table'[CandidateNo]
VAR _index = 'Table'[Index]
RETURN
COUNTROWS(
FILTER ('Table',
'Table'[CandidateNo] = _CandidateNo &&
'Table'[Is less than or eq 4] = 0 &&
'Table'[Index]<= _index)
)
GroupStartDate Column =
CALCULATE (
MIN ( 'Table'[AssignmentStartDate] ),
ALLEXCEPT('Table','Table'[CandidateNo],'Table'[Count 0 Value])
)
GroupEndDate Column =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
ALLEXCEPT('Table','Table'[CandidateNo],'Table'[Count 0 Value])
)
The only issue I am facing is that first column "Is less than or eq 4" there is no condition for candidate. I was able to consider candidate context in all other columns but I was not able to in this column. As a result if the first assignment for specific candidate is starting after gap of 4 days then value will be coming as 0 for first assignment itself for candidate. But ideally it should be 1 as its first assignment for that candidate.
Please find below the sample data.
Please see 2nd row. The value of "less thn or equal to 4" is coming as 0 as I am unable to pass candidate context and its just considering Index.
That value should be 1.
Thanks in Advance!
AssignmentStartDate | AssignmentEndDate | CandidateNo | Index | Less thn or equal to 4 | Count 0 Value | GroupStartDate | GroupEndDate |
Monday, 13 March 2000 | Friday, 16 June 2000 | 111 | 326 | 1 | Monday, 13 March 2000 | Friday, 16 June 2000 | |
Monday, 30 October 2000 | Sunday, 27 January 2002 | 222 | 327 | 0 | 1 | Monday, 30 October 2000 | Sunday, 10 February 2002 |
Monday, 28 January 2002 | Sunday, 10 February 2002 | 222 | 328 | 1 | 1 | Monday, 30 October 2000 | Sunday, 10 February 2002 |
Monday, 22 April 2002 | Tuesday, 31 December 2002 | 222 | 329 | 0 | 2 | Monday, 22 April 2002 | Tuesday, 30 September 2003 |
Wednesday, 1 January 2003 | Monday, 31 March 2003 | 222 | 330 | 1 | 2 | Monday, 22 April 2002 | Tuesday, 30 September 2003 |
Tuesday, 1 April 2003 | Tuesday, 30 September 2003 | 222 | 331 | 1 | 2 | Monday, 22 April 2002 | Tuesday, 30 September 2003 |
Monday, 14 July 2003 | Friday, 26 September 2003 | 222 | 332 | 1 | 2 | Monday, 22 April 2002 | Tuesday, 30 September 2003 |
Wednesday, 1 October 2003 | Saturday, 31 January 2004 | 222 | 333 | 0 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Wednesday, 1 October 2003 | Saturday, 31 January 2004 | 222 | 334 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Sunday, 1 February 2004 | Wednesday, 30 June 2004 | 222 | 335 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Sunday, 1 February 2004 | Sunday, 11 July 2004 | 222 | 336 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Thursday, 1 July 2004 | Sunday, 27 February 2005 | 222 | 337 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 12 July 2004 | Friday, 1 July 2005 | 222 | 338 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Saturday, 2 July 2005 | Sunday, 2 July 2006 | 222 | 339 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 3 July 2006 | Monday, 2 July 2007 | 222 | 340 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Tuesday, 3 July 2007 | Monday, 30 June 2008 | 222 | 341 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Tuesday, 1 July 2008 | Sunday, 6 July 2008 | 222 | 342 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 7 July 2008 | Tuesday, 30 June 2009 | 222 | 343 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Wednesday, 1 July 2009 | Thursday, 31 December 2009 | 222 | 344 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Friday, 1 January 2010 | Wednesday, 30 June 2010 | 222 | 345 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Thursday, 1 July 2010 | Friday, 31 December 2010 | 222 | 346 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Saturday, 1 January 2011 | Saturday, 31 December 2011 | 222 | 347 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Sunday, 1 January 2012 | Sunday, 30 December 2012 | 222 | 348 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 31 December 2012 | Friday, 8 February 2013 | 222 | 349 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Saturday, 9 February 2013 | Friday, 24 May 2013 | 222 | 350 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 27 May 2013 | Tuesday, 31 December 2013 | 222 | 351 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Wednesday, 1 January 2014 | Friday, 27 June 2014 | 222 | 352 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Tuesday, 1 July 2014 | Tuesday, 30 September 2014 | 222 | 353 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Wednesday, 1 October 2014 | Friday, 31 October 2014 | 222 | 354 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Saturday, 1 November 2014 | Friday, 14 November 2014 | 222 | 355 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
I tweaked "Is less than or eq 4" as below and its working as expected.
Thank you very much for all your help!
Is less than or eq 4 =
VAR PreviousIndex = [Index] - 1
VAR ThisStartDate = [AssignmentStartDate]
VAR PreviuosEndDate =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
FILTER ( 'Table', 'Table'[Index] = PreviousIndex )
)
VAR DateDiff_ =
DATEDIFF ( PreviuosEndDate, ThisStartDate, DAY ) + 0
VAR FirstIndex =
CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[CandidateNo]))
VAR Flag=IF(NoOFDays<=56,1,0)
Return
IF('Table'[Index]=FirstIndex,1,Flag)
Hello All,
Thanks for all your help!
I have created GroupStartDate and GroupEndDate in very easy way using allexcept and it working as expected.
Please find below all the calculated columns:
Is less than or eq 4 =
VAR PreviousIndex = [Index] - 1
VAR ThisStartDate = [AssignmentStartDate]
VAR PreviuosEndDate =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
FILTER ( 'Table', 'Table'[Index] = PreviousIndex )
)
VAR DateDiff_ =
DATEDIFF ( PreviuosEndDate, ThisStartDate, DAY ) + 0
RETURN
IF ( DateDiff_ <= 4, 1, 0 )
Count 0 Value =
VAR _CandidateNo ='Table'[CandidateNo]
VAR _index = 'Table'[Index]
RETURN
COUNTROWS(
FILTER ('Table',
'Table'[CandidateNo] = _CandidateNo &&
'Table'[Is less than or eq 4] = 0 &&
'Table'[Index]<= _index)
)
GroupStartDate Column =
CALCULATE (
MIN ( 'Table'[AssignmentStartDate] ),
ALLEXCEPT('Table','Table'[CandidateNo],'Table'[Count 0 Value])
)
GroupEndDate Column =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
ALLEXCEPT('Table','Table'[CandidateNo],'Table'[Count 0 Value])
)
The only issue I am facing is that first column "Is less than or eq 4" there is no condition for candidate. I was able to consider candidate context in all other columns but I was not able to in this column. As a result if the first assignment for specific candidate is starting after gap of 4 days then value will be coming as 0 for first assignment itself for candidate. But ideally it should be 1 as its first assignment for that candidate.
Please find below the sample data.
Please see 2nd row. The value of "less thn or equal to 4" is coming as 0 as I am unable to pass candidate context and its just considering Index.
That value should be 1.
Thanks in Advance!
AssignmentStartDate | AssignmentEndDate | CandidateNo | Index | Less thn or equal to 4 | Count 0 Value | GroupStartDate | GroupEndDate |
Monday, 13 March 2000 | Friday, 16 June 2000 | 111 | 326 | 1 | Monday, 13 March 2000 | Friday, 16 June 2000 | |
Monday, 30 October 2000 | Sunday, 27 January 2002 | 222 | 327 | 0 | 1 | Monday, 30 October 2000 | Sunday, 10 February 2002 |
Monday, 28 January 2002 | Sunday, 10 February 2002 | 222 | 328 | 1 | 1 | Monday, 30 October 2000 | Sunday, 10 February 2002 |
Monday, 22 April 2002 | Tuesday, 31 December 2002 | 222 | 329 | 0 | 2 | Monday, 22 April 2002 | Tuesday, 30 September 2003 |
Wednesday, 1 January 2003 | Monday, 31 March 2003 | 222 | 330 | 1 | 2 | Monday, 22 April 2002 | Tuesday, 30 September 2003 |
Tuesday, 1 April 2003 | Tuesday, 30 September 2003 | 222 | 331 | 1 | 2 | Monday, 22 April 2002 | Tuesday, 30 September 2003 |
Monday, 14 July 2003 | Friday, 26 September 2003 | 222 | 332 | 1 | 2 | Monday, 22 April 2002 | Tuesday, 30 September 2003 |
Wednesday, 1 October 2003 | Saturday, 31 January 2004 | 222 | 333 | 0 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Wednesday, 1 October 2003 | Saturday, 31 January 2004 | 222 | 334 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Sunday, 1 February 2004 | Wednesday, 30 June 2004 | 222 | 335 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Sunday, 1 February 2004 | Sunday, 11 July 2004 | 222 | 336 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Thursday, 1 July 2004 | Sunday, 27 February 2005 | 222 | 337 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 12 July 2004 | Friday, 1 July 2005 | 222 | 338 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Saturday, 2 July 2005 | Sunday, 2 July 2006 | 222 | 339 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 3 July 2006 | Monday, 2 July 2007 | 222 | 340 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Tuesday, 3 July 2007 | Monday, 30 June 2008 | 222 | 341 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Tuesday, 1 July 2008 | Sunday, 6 July 2008 | 222 | 342 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 7 July 2008 | Tuesday, 30 June 2009 | 222 | 343 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Wednesday, 1 July 2009 | Thursday, 31 December 2009 | 222 | 344 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Friday, 1 January 2010 | Wednesday, 30 June 2010 | 222 | 345 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Thursday, 1 July 2010 | Friday, 31 December 2010 | 222 | 346 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Saturday, 1 January 2011 | Saturday, 31 December 2011 | 222 | 347 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Sunday, 1 January 2012 | Sunday, 30 December 2012 | 222 | 348 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 31 December 2012 | Friday, 8 February 2013 | 222 | 349 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Saturday, 9 February 2013 | Friday, 24 May 2013 | 222 | 350 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Monday, 27 May 2013 | Tuesday, 31 December 2013 | 222 | 351 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Wednesday, 1 January 2014 | Friday, 27 June 2014 | 222 | 352 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Tuesday, 1 July 2014 | Tuesday, 30 September 2014 | 222 | 353 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Wednesday, 1 October 2014 | Friday, 31 October 2014 | 222 | 354 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
Saturday, 1 November 2014 | Friday, 14 November 2014 | 222 | 355 | 1 | 3 | Wednesday, 1 October 2003 | Friday, 14 November 2014 |
I tweaked "Is less than or eq 4" as below and its working as expected.
Thank you very much for all your help!
Is less than or eq 4 =
VAR PreviousIndex = [Index] - 1
VAR ThisStartDate = [AssignmentStartDate]
VAR PreviuosEndDate =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
FILTER ( 'Table', 'Table'[Index] = PreviousIndex )
)
VAR DateDiff_ =
DATEDIFF ( PreviuosEndDate, ThisStartDate, DAY ) + 0
VAR FirstIndex =
CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[CandidateNo]))
VAR Flag=IF(NoOFDays<=56,1,0)
Return
IF('Table'[Index]=FirstIndex,1,Flag)
Hi @Anonymous ,
First, please add an Index column in Power Query Editor.
And then, try to create calculated columns like so:
Is less than or eq 4 =
VAR PreviousIndex = [Index] - 1
VAR ThisStartDate = [AssignmentStartDate]
VAR PreviuosEndDate =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
FILTER ( 'Table', 'Table'[Index] = PreviousIndex )
)
VAR DateDiff_ =
DATEDIFF ( PreviuosEndDate, ThisStartDate, DAY ) + 0
RETURN
IF ( DateDiff_ <= 4, 1, 0 )
Count 0 Value =
CALCULATE (
COUNT ( 'Table'[Is less than or eq 4] ),
FILTER (
'Table',
'Table'[Is less than or eq 4] = 0
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
)
GroupStartDate Column =
CALCULATE (
MIN ( 'Table'[AssignmentStartDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[Count 0 Value] = EARLIER ( 'Table'[Count 0 Value] )
)
)
GroupEndDate Column =
CALCULATE (
MAX ( 'Table'[AssignmentEndDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] >= EARLIER ( 'Table'[Index] )
&& 'Table'[Count 0 Value] = EARLIER ( 'Table'[Count 0 Value] )
)
)
For more details, please check the attached .pbix file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for reply!
I am getting expected result by using above solution in case if i restrict data for lets say 2 candidates(around 10 rows).
I am having around 70 thousand rows in my data and getting memory error while calculating "count 0" column.
Is there any way by which we can tweak it?
Waiting for your reply.
Thanks in Advance!
The provided code doesn't use the CandidateNo (quite correct since the provided data has the same candidateNo).
The real data will have different Candidates. Are the new Group columns dependent on Candidate?
In other words, if the real requirement is "If next assignment of [the same] candidate is starting in less then =4 days then it should be in one group or else new group should be started".
then the code can be altered to use the CandidateNo. This will speed things up and use less memory. I suggest testing with less than 70 thousand rows.
If the Group columns are not dependent on CandidateNo, please ignore me (!)
Apologes!
That was my bad that I forgot to mention that there will be multiple users.
I have altered the count 0 column as :
Did the DAX return the correct result? No errors?
Which column needs a performance improvement?
Hello,
When tested for 3 candidates (around 15 rows) with altered "count 0" column mentioned by me in above post it is giving desired result.
But when I am testing for complete data (70K rows) I am getting error in calculating "Count 0" column itself and hence unable to reach result.
It is giving me error as memory issue.
As mentioned by you if there is going to be grouping for each candidate then we can tweak the Dax to give faster result.
Yes there will be grouping for each candidate.
Request you to please help in tweaking DAX.
I hope I am making sense.
Thanks in Advance!
I've come up with this to eliminate the context transition
ColumnTest =
VAR _CandidateNo = TableA[CandidateNo]
VAR _index = TableA[Index]
RETURN
COUNTROWS(
FILTER (TableA,
TableA[CandidateNo] = _CandidateNo &&
TableA[Is less than or eq 4] = 0 &&
TableA[Index] <= _index)
)
I've not tested it for performance so see how you get on.
Further performance tips :
1. create the columns in Power Query instead.
2. Use measures instead of calculated columns
3. Switch off 'Auto date/time' in Options
4. Make sure date fields are Date type (not date/time - if you don't need it)
5. Reduce the columns in the Fact table (There are not many in the provided sample data but maybe the real data has more)
Hi @HotChilli
Thank you very much for help! Much appreciated 🙂
The DAX provided by you actually worked and giving me same result for Count 0 as previous.
But now next column which needs to be calculated is giving me performance error. I tried to tweak it as suggested by you for count 0 column but unfortunately no luck. Please find below changes done by me.
Old :
@Anonymous , Please see if this file can help.
https://www.dropbox.com/s/8rzsbh7mzbc59wm/Continuous_Streak_discont_dates.pbix?dl=0
I missed few updates in between, so still inline with initial solution
@Anonymous , refer if this can help
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |