Counting matches within a table based on two other columns

[Anonymous]

Hi All,

I have a table of history data (one row per event) that looks like:

The _primarykey here is actually that of a parent table, not the history table... the parent item can have multiple rows in the history table.

What I'm looking for is the DAX to populate the "Renewed" column, based on finding another row (for the same parent primary key) that has a 'Start Date' that matches the 'End Date' of the current row.

I've tried the following as a calculated column, but i get a syntax error:

Renewed =
VAR vkey = history[_primarykey]
VAR vdate = history[End Date]
RETURN
    IF (
        COUNTROWS (
            FILTER (
                FILTER ( FILTER ( history, ALL ), history[_primarykey] = vkey ),
                history[Start Date] = vdate
            )
        )
            > 0,
        "Yes",
        "No"
    )

The logic being to expand the table to ALL rows, then filter back down to match the (parent) primary key, and then the dates, and to count the rows of that table... if the count is greater than zero then 'Renewed' = "Yes".

All help appreciated.

[Greg_Deckler]

Why not just:

Renewed Column = IF([EndDate] IN ALL(Matches[StartDate]),"Yes","No")

[Anonymous]

Hi @Greg_Deckler... well I tried to do it with table filters and counts because I hadn't yet come across the IN operator...

The solution suggested doesn't take into account the match on the parent _primarykey also though... I need to match two items between the rows. Does IN handle multiple columns?

[Greg_Deckler]

OK, try this then:

Renewed = IF([EndDate] IN FILTER(ALL(Matches[StartDate]),[_primarykey]=EARLIER([_primarykey])),"Yes","No")

[Zubair_Muhammad · Accepted solution]

@Anonymous

This Column should work as well

Renewed =
VAR myenddate = Table1[End Date]
RETURN
    IF (
        CONTAINS (
            FILTER ( Table1, Table1[_primarykey] = EARLIER ( Table1[_primarykey] ) ),
            Table1[Start Date], myenddate
        ),
        "Yes",
        "No"
    )

[Zubair_Muhammad]

@Anonymous