DAX: If same value in one of the previous rows, then 0, else 1

[Titatovenaar]

Hi,

I have a Calculated Column in which I want to show whether a 'Table.ContractId' became active. However, the dataset I work with sometimes shows for a certain 'Table.Date' the 'Table.ContractId' twice.

This dataset/Table I base my solution on, works the following way: every month it gives an overview of all contracts with their 'Table.ValidFrom' and 'Table.ValidTo' dates. However, as soon as there is a 'Table.ValidTo' WITHIN that same period, it will create another record with the 'Table.Date' set to that 'Table.ValidTo' within same period date.

Currently this is my measure 'CURRENT' in column 5 with a nested IF statement:

CURRENT =
IF(Table[ValidFrom].[Date] = Table[DATE].[Date] || (Table[ValidFrom].[Date] >= Table[DATE].[Date] && Table[ValidFrom].[Date] < DATEADD(Table[DATE].[Date];1;MONTH));
IF(Table[DATE].[Date] = Table[ValidTo].[Date];
IF(Table[ValidFrom] = Table[ValidTo];
IF(Table[ValidFrom].[Date] = Table[DATE].[Date] && PMCCONTRACTMUTATIONS[ValidTo].[Date] = Table[DATE].[Date];
0 --this is the Zero that in the first exact case should say 1 instead of 0 !
; 1); 0); 1); 0)

Here it checks whether 'Table.ValidFrom' is the same as 'Table.Date',

whether 'Table.ValidFrom' is within the period (month) of 'Table.Date', 

whether 'Table.ValidTo' is equal to the 'Table.Date', WITHIN that same period, since that means there will be a duplicate value.

Though, the question is, how to recognize that duplicate value. Besides, this might not necessarily be the PREVIOUS RECORD, it might be ONE OF THE PREVIOUS RECORDS.

CONTRACTID	DATE	ValidFrom	ValidTo	CURRENT	EXPECTED
HC012473	1-1-2015 00:00	14-9-2006 00:00	22-5-2013 00:00	0	0
HC025087	1-6-2015 00:00	25-6-2015 00:00	25-6-2015 00:00	1	1
HC025087	25-6-2015 00:00	25-6-2015 00:00	25-6-2015 00:00	0	0
HC076719	1-2-2017 00:00	1-2-2017 00:00	1-2-2017 00:00	0	0
HC076719	1-2-2017 00:00	1-2-2017 00:00	1-2-2017 00:00	0	1
HC074791	1-1-2018 00:00	1-1-2018 00:00	1-1-2018 00:00	0	0
HC074791	1-1-2018 00:00	1-1-2018 00:00	1-1-2018 00:00	0	1
HC079152	1-12-2018 00:00	1-12-2018 00:00	1-12-2018 00:00	0	0
HC079152	1-12-2018 00:00	1-12-2018 00:00	1-12-2018 00:00	0	1

Kind regards,

Igor

[v-shex-msft]

HI @Titatovenaar,

You can add an index column to your table and use the following calculated column formula to remark records based on index column:

CURRENT NEW =
VAR temp =
    ADDCOLUMNS (
        'Table',
        "Duplicate", IF ( [DATE] = [ValidFrom] && [DATE] = [ValidTo], 0, 1 )
    )
VAR dupCount =
    COUNTX (
        FILTER (
            SUMMARIZE ( temp, [CONTRACTID], [Duplicate] ),
            [CONTRACTID] = EARLIER ( 'Table'[CONTRACTID] )
        ),
        [Duplicate]
    )
VAR mRowCount =
    COUNTROWS ( FILTER ( 'Table', [CONTRACTID] = EARLIER ( [CONTRACTID] ) ) )
VAR cRowCount =
    COUNTROWS (
        FILTER (
            'Table',
            [CONTRACTID] = EARLIER ( [CONTRACTID] )
                && [Index] <= EARLIER ( 'Table'[Index] )
        )
    )
RETURN
    IF (
        dupCount > 1,
        IF ( [DATE] = [ValidFrom] && [DATE] = [ValidTo], 0, 1 ),
        IF ( cRowCount = mRowCount && mRowCount > 1, 1, 0 )
    )

Regards,

Xiaoxin Sheng

[AllisonKennedy]

@Titatovenaar  I don't know fully what your question is, but I think you might find the EARLIER function helpful here.

You can use EARLIER in combination with FILTER or other table function. What EARLIER does is enable you to compare one value in the current row context to the PREVIOUS ROW CONTEXT of the DAX expression, so when used correctly it can enable you to compare the current row to each and every other row in the table.

If you clarify your question we can try to provide you with a more specific answer, but research EARLIER if you haven't looked into it already. 

[Titatovenaar]

Thx for the fast reply.

I will definitely check 'EARLIER' functionality right now.

What I simply want, if you forget about all the data set and the measure above is IF all 3 columns are the same, THEN a 0, UNLESS the exact same combination of values happened before.

ID, Col1, Col2, Col3, MEASURE

1, Y, X, X, 0

2, X, X, X, 1

3, X, Y, Y, 0

4, X, X, X, 0

Kind regards,

Igor

[AllisonKennedy]

@Titatovenaar  Thanks, that clarification helps a bit, but I'm still a little confused. Your sample table doesn't seem to match what you wrote in the paragraph?? If columns don't match it should also be 0? 

Also, does contract number matter? In your sample table it seems like it does not, but I'm assuming it should from looking at your original post?

I would recommend creating a rank (similar to @v-shex-msft 's advice to create Index column), to help order the lines by date: 

RANK = RANKX('Table','Table'[Date])

If contract number matters, you may wish to rank within each contract. 

Then, do something similar to the below, which gets the expected result in your given table: 

Column = IF('Table'[Date]='Table'[Valid From]&& 'Table'[Date]='Table'[Valid To],COMBINEVALUES(";",

--'Table'[Contract],

'Table'[Date],'Table'[Valid From],'Table'[Valid To]),FORMAT('Table'[RANK],""))

 

*Note in Column you may wish to add Contract or not, depending on if you care about looking at the three values within each contract, or overall. 

Column 2 =

IF(

COUNTROWS(FILTER('Table','Table'[RANK]<EARLIER('Table'[RANK])

&& 'Table'[Column]=EARLIER('Table'[Column])

))

>0, 1,0)

[Anonymous]

Thanks for the extensive replies!

Xiaoxin Sheng's solution so far I couldn't try, because I cannot make a unique row index based on my duplicate string/date fields in my table. Or is there a workaround for that? I only see measures doing that row_count trick, not in a calculated column.

AllisonKennedy, your comment brought me closer to the solution: I now flagged the duplicates with the EARLIER functionality. However, I want only the first instances of the duplicates to get a certain value. So far this is my dataset, in which the column Duplicate uses COUNTROWS functionality to count the duplicates.

ContractId	Date	ValidFrom	ValidTo	Duplicate	FirstInstance
1	1-1-2015	1-1-2006	22-5-2013	1	0
2	1-6-2015	25-6-2015	25-6-2015	1	0
2	25-6-2015	25-6-2015	25-6-2015	1	0
3	1-2-2017	1-2-2017	1-2-2017	2	1
3	1-2-2017	1-2-2017	1-2-2017	2	0
4	1-1-2018	1-1-2018	1-1-2018	2	1
4	1-1-2018	1-1-2018	1-1-2018	2	0

I can then later add this FirstInstance to my previously mentioned IF statement, since it did not include those first instances yet! Maybe not the most beautiful, but it does the trick!

Kind regards,

Igor

[v-shex-msft]

HI @Anonymous,

You can refer to the following link to add an index column to your table in quey editor side:

Power Query Helps To Create Index Column 

Regards,

Xiaoxin Sheng