Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
diegojustino
Frequent Visitor

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

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 numberRO eventClaimPerformedEntry
10205012485701/05/16ReparoFirst entry
10204512571001/07/16Troca 
10216512559701/07/16Troca 
10207012565401/07/16ReparoFirst entry
10204512564301/22/16Troca 
10205012589701/22/16ReparoSecond entry
10216512544001/22/16ReparoFirst entry
10205012546802/13/16ReparoThird entry or more
10207012551202/21/16ReparoSecond entry
10216512533006/13/16Troca 
10216512585806/23/16ReparoSecond entry
10216512568806/23/16ReparoThird entry or more
10205012432506/23/16ReparoThird entry or more
10207012534206/23/16ReparoThird entry or more
10204512585007/13/16ReparoFirst entry
10216512544407/13/16Troca 
10205012569107/14/16ReparoThird entry or more
10205012573508/15/16ReparoThird entry or more
10205412587010/06/16ReparoFirst entry
10205012485610/06/16ReparoThird entry or more
10204512541010/06/16ReparoSecond entry
10207012503010/06/16ReparoThird entry or more

 

column in red should be created 

column in blue should be considered for calculate

 

Best regards,

Diego Justino.

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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.

 

t1.PNG

 

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 )
        )
    )

e1.PNG

 

 

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" )
    )
)

e2.PNG

 

Regards

View solution in original post

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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.

 

t1.PNG

 

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 )
        )
    )

e1.PNG

 

 

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" )
    )
)

e2.PNG

 

Regards

Hi, @v-ljerr-msft

 

Tks, the solution is perfect!

 

Best regards,

Diego Justino.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.