Need help in DAX

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! 

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)