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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help in DAX

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.

 

AssignmentStartDateAssignmentEndDateCandidateNoGroupStartDateGroupEndDate
06-04-201006-10-201012306-04-202027-02-2011
07-10-201012-11-201012306-04-202027-02-2011
13-11-201027-02-201112306-04-202027-02-2011
25-07-201131-12-201112325-07-201115-04-2012
03-01-201215-04-201212325-07-201115-04-2012
     

 

Request you to please help here.

Thanks!

4 ACCEPTED SOLUTIONS
Icey
Community Support
Community Support

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] )
    )
)

column.PNG

 

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.

View solution in original post

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)

View solution in original post

Anonymous
Not applicable

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! 

 

AssignmentStartDateAssignmentEndDateCandidateNoIndexLess thn or equal to 4Count 0 ValueGroupStartDateGroupEndDate
Monday, 13 March 2000Friday, 16 June 20001113261 Monday, 13 March 2000Friday, 16 June 2000
Monday, 30 October 2000Sunday, 27 January 200222232701Monday, 30 October 2000Sunday, 10 February 2002
Monday, 28 January 2002Sunday, 10 February 200222232811Monday, 30 October 2000Sunday, 10 February 2002
Monday, 22 April 2002Tuesday, 31 December 200222232902Monday, 22 April 2002Tuesday, 30 September 2003
Wednesday, 1 January 2003Monday, 31 March 200322233012Monday, 22 April 2002Tuesday, 30 September 2003
Tuesday, 1 April 2003Tuesday, 30 September 200322233112Monday, 22 April 2002Tuesday, 30 September 2003
Monday, 14 July 2003Friday, 26 September 200322233212Monday, 22 April 2002Tuesday, 30 September 2003
Wednesday, 1 October 2003Saturday, 31 January 200422233303Wednesday, 1 October 2003Friday, 14 November 2014
Wednesday, 1 October 2003Saturday, 31 January 200422233413Wednesday, 1 October 2003Friday, 14 November 2014
Sunday, 1 February 2004Wednesday, 30 June 200422233513Wednesday, 1 October 2003Friday, 14 November 2014
Sunday, 1 February 2004Sunday, 11 July 200422233613Wednesday, 1 October 2003Friday, 14 November 2014
Thursday, 1 July 2004Sunday, 27 February 200522233713Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 12 July 2004Friday, 1 July 200522233813Wednesday, 1 October 2003Friday, 14 November 2014
Saturday, 2 July 2005Sunday, 2 July 200622233913Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 3 July 2006Monday, 2 July 200722234013Wednesday, 1 October 2003Friday, 14 November 2014
Tuesday, 3 July 2007Monday, 30 June 200822234113Wednesday, 1 October 2003Friday, 14 November 2014
Tuesday, 1 July 2008Sunday, 6 July 200822234213Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 7 July 2008Tuesday, 30 June 200922234313Wednesday, 1 October 2003Friday, 14 November 2014
Wednesday, 1 July 2009Thursday, 31 December 200922234413Wednesday, 1 October 2003Friday, 14 November 2014
Friday, 1 January 2010Wednesday, 30 June 201022234513Wednesday, 1 October 2003Friday, 14 November 2014
Thursday, 1 July 2010Friday, 31 December 201022234613Wednesday, 1 October 2003Friday, 14 November 2014
Saturday, 1 January 2011Saturday, 31 December 201122234713Wednesday, 1 October 2003Friday, 14 November 2014
Sunday, 1 January 2012Sunday, 30 December 201222234813Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 31 December 2012Friday, 8 February 201322234913Wednesday, 1 October 2003Friday, 14 November 2014
Saturday, 9 February 2013Friday, 24 May 201322235013Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 27 May 2013Tuesday, 31 December 201322235113Wednesday, 1 October 2003Friday, 14 November 2014
Wednesday, 1 January 2014Friday, 27 June 201422235213Wednesday, 1 October 2003Friday, 14 November 2014
Tuesday, 1 July 2014Tuesday, 30 September 201422235313Wednesday, 1 October 2003Friday, 14 November 2014
Wednesday, 1 October 2014Friday, 31 October 201422235413Wednesday, 1 October 2003Friday, 14 November 2014
Saturday, 1 November 2014Friday, 14 November 201422235513Wednesday, 1 October 2003Friday, 14 November 2014

View solution in original post

Anonymous
Not applicable

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)

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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! 

 

AssignmentStartDateAssignmentEndDateCandidateNoIndexLess thn or equal to 4Count 0 ValueGroupStartDateGroupEndDate
Monday, 13 March 2000Friday, 16 June 20001113261 Monday, 13 March 2000Friday, 16 June 2000
Monday, 30 October 2000Sunday, 27 January 200222232701Monday, 30 October 2000Sunday, 10 February 2002
Monday, 28 January 2002Sunday, 10 February 200222232811Monday, 30 October 2000Sunday, 10 February 2002
Monday, 22 April 2002Tuesday, 31 December 200222232902Monday, 22 April 2002Tuesday, 30 September 2003
Wednesday, 1 January 2003Monday, 31 March 200322233012Monday, 22 April 2002Tuesday, 30 September 2003
Tuesday, 1 April 2003Tuesday, 30 September 200322233112Monday, 22 April 2002Tuesday, 30 September 2003
Monday, 14 July 2003Friday, 26 September 200322233212Monday, 22 April 2002Tuesday, 30 September 2003
Wednesday, 1 October 2003Saturday, 31 January 200422233303Wednesday, 1 October 2003Friday, 14 November 2014
Wednesday, 1 October 2003Saturday, 31 January 200422233413Wednesday, 1 October 2003Friday, 14 November 2014
Sunday, 1 February 2004Wednesday, 30 June 200422233513Wednesday, 1 October 2003Friday, 14 November 2014
Sunday, 1 February 2004Sunday, 11 July 200422233613Wednesday, 1 October 2003Friday, 14 November 2014
Thursday, 1 July 2004Sunday, 27 February 200522233713Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 12 July 2004Friday, 1 July 200522233813Wednesday, 1 October 2003Friday, 14 November 2014
Saturday, 2 July 2005Sunday, 2 July 200622233913Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 3 July 2006Monday, 2 July 200722234013Wednesday, 1 October 2003Friday, 14 November 2014
Tuesday, 3 July 2007Monday, 30 June 200822234113Wednesday, 1 October 2003Friday, 14 November 2014
Tuesday, 1 July 2008Sunday, 6 July 200822234213Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 7 July 2008Tuesday, 30 June 200922234313Wednesday, 1 October 2003Friday, 14 November 2014
Wednesday, 1 July 2009Thursday, 31 December 200922234413Wednesday, 1 October 2003Friday, 14 November 2014
Friday, 1 January 2010Wednesday, 30 June 201022234513Wednesday, 1 October 2003Friday, 14 November 2014
Thursday, 1 July 2010Friday, 31 December 201022234613Wednesday, 1 October 2003Friday, 14 November 2014
Saturday, 1 January 2011Saturday, 31 December 201122234713Wednesday, 1 October 2003Friday, 14 November 2014
Sunday, 1 January 2012Sunday, 30 December 201222234813Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 31 December 2012Friday, 8 February 201322234913Wednesday, 1 October 2003Friday, 14 November 2014
Saturday, 9 February 2013Friday, 24 May 201322235013Wednesday, 1 October 2003Friday, 14 November 2014
Monday, 27 May 2013Tuesday, 31 December 201322235113Wednesday, 1 October 2003Friday, 14 November 2014
Wednesday, 1 January 2014Friday, 27 June 201422235213Wednesday, 1 October 2003Friday, 14 November 2014
Tuesday, 1 July 2014Tuesday, 30 September 201422235313Wednesday, 1 October 2003Friday, 14 November 2014
Wednesday, 1 October 2014Friday, 31 October 201422235413Wednesday, 1 October 2003Friday, 14 November 2014
Saturday, 1 November 2014Friday, 14 November 201422235513Wednesday, 1 October 2003Friday, 14 November 2014
Anonymous
Not applicable

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)
Icey
Community Support
Community Support

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] )
    )
)

column.PNG

 

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.

Anonymous
Not applicable

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 (!)

Anonymous
Not applicable

Apologes!

That was my bad that I forgot to mention that there will be multiple users.

I have altered the count 0 column as : 

 

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] )
&& 'Table'[CandidateNo] = EARLIER ( 'Table'[CandidateNo])
)
)
 
Request you to please let us know if we can modify dax which will give faster result.
Thanks!

Did the DAX return the correct result?  No errors?

 

Which column needs a performance improvement?

Anonymous
Not applicable

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)

Anonymous
Not applicable

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 :

GroupStartDate Column =
CALCULATE (
MIN ( 'Table'[AssignmentStartDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[Count 0 Value] = EARLIER ( 'Table'[Count 0 Value] )
)
)
New:
Column =
VAR _Index= 'Table'[Index]
VAR _count0= 'Table'[Count 0 Value]
Return
CALCULATE(MIN('Table'[AssignmentStartDate]),
Filter('Table',
'Table'[Count 0 Value]=_count0 &&
'Table'[Index]<=_Index)
)
On the test data the new one is giving be same result as old but in my actual data I am facing memory issue.
I have removed not required extra columns from table and also converted datetime to date.
Thanks in Advance!

@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 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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