Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Need help in DAX

‎08-06-2020 04:21 AM

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!

Labels:
Message 1 of 13
974 Views
0
4 ACCEPTED SOLUTIONS
Icey
Community Support
Community Support

‎08-07-2020 12:50 AM

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

Need help in DAX.pbix
Message 3 of 13
901 Views
HotChilli
Super User
Super User
In response to Anonymous

‎08-10-2020 02:14 PM

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

Message 9 of 13
820 Views
0
Anonymous
Not applicable

‎08-11-2020 03:48 AM

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

Message 12 of 13
825 Views
0
Anonymous
Not applicable
In response to Anonymous

‎08-14-2020 03:44 AM

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

Message 13 of 13
806 Views
0
12 REPLIES 12

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All