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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.