New column flagged as first entry, second entry, or other.

[diegojustino]

Hi,

I have a table where I need to create a column that should signal if it is the first, second or other entries, according to some parameters.

Example: I want to see the first date of the claim column for each number in the RO number column and also has the column performed as "Reparo". 

If the case of RO number "102050" the first entry was claim "01/05/2016"

RO number	RO event	Claim	Performed	Entry
102050	124857	01/05/16	Reparo	First entry
102045	125710	01/07/16	Troca
102165	125597	01/07/16	Troca
102070	125654	01/07/16	Reparo	First entry
102045	125643	01/22/16	Troca
102050	125897	01/22/16	Reparo	Second entry
102165	125440	01/22/16	Reparo	First entry
102050	125468	02/13/16	Reparo	Third entry or more
102070	125512	02/21/16	Reparo	Second entry
102165	125330	06/13/16	Troca
102165	125858	06/23/16	Reparo	Second entry
102165	125688	06/23/16	Reparo	Third entry or more
102050	124325	06/23/16	Reparo	Third entry or more
102070	125342	06/23/16	Reparo	Third entry or more
102045	125850	07/13/16	Reparo	First entry
102165	125444	07/13/16	Troca
102050	125691	07/14/16	Reparo	Third entry or more
102050	125735	08/15/16	Reparo	Third entry or more
102054	125870	10/06/16	Reparo	First entry
102050	124856	10/06/16	Reparo	Third entry or more
102045	125410	10/06/16	Reparo	Second entry
102070	125030	10/06/16	Reparo	Third entry or more

column in red should be created 

column in blue should be considered for calculate

Best regards,

Diego Justino.

[v-ljerr-msft · Accepted solution]

Hi @diegojustino,

According to your description, you should be able to use RANK.EQ Function (DAX) to create a calculate column to flag the entries in this scenario.

I assume you have a table called "Table1" like below.

1. Use the formula below to create a calculate column to get the number indicating which entry it should be.

Entry1 = 
VAR r = Table1[RO number]
VAR c = Table1[Claim]
VAR p = Table1[Performed]
RETURN
    IF (
        p = "Reparo",
        CALCULATE (
            RANK.EQ ( c, Table1[Claim], ASC ),
            FILTER ( ALL ( Table1 ), Table1[RO number] = r && Table1[Performed] = p )
        )
    )

2. Then use the formula below to flag the entry.

Entry2 = 
IF (
    Table1[Entry1] = 1,
    "First Entry",
    IF (
        Table1[Entry1] = 2,
        "Second Entry",
        IF ( Table1[Entry1] >= 3, "Third Entry or More" )
    )
)

Regards

[diegojustino]

Hi, @v-ljerr-msft

Tks, the solution is perfect!

Best regards,

Diego Justino.