Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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