Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to tag first occurrence with a filter

Hello, I want to flag the first occurrence of accounts based on a date, with a filter (it should only consider if ACTIVE=yes), and if there are duplicates i need to choose only one of them (randomly/...
"DAX help"
"Help"
"Need Help"
  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, Anonymous 

     

    Earlier() function use when you have two or more row context and you want to use outer row context value refrence as current.

     

    in our case we use code in calculated column so it give first row context and  maxx it give second row context and we want to use refrence of  Account column .

     

    when we move row by row in our table first row refer account X and we move second row it still refer X  and we compare it with current acoount column value

    in some case it syntax sugar of variable
    below same code for same result

    Column 2 = 
var a1= 'Table (2)'[Account]
var a = 
    MAXX(
      FILTER(
         'Table (2)',
         'Table (2)'[Account]=a1 && 
         'Table (2)'[Active]="yes"),'Table (2)'[Index]
    )
return
IF('Table (2)'[Index]=a,1,0)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

hi, Anonymous 

i think you need to add index because same date present with same account and same active 
so go to power query >add column> index column
close power query window
and add new column with below code

Column 2 = 
var a = MAXX(FILTER('Table (2)','Table (2)'[Account]=EARLIER('Table (2)'[Account])&& 'Table (2)'[Active]="yes"),'Table (2)'[Index])
return
IF('Table (2)'[Index]=a,1,0)

 

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hello, thank you for your response, it works.

 

Just wanted to ask what is the role of 'earlier' function here? Would it be ok if we write the formula without it?

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, Anonymous 

     

    Earlier() function use when you have two or more row context and you want to use outer row context value refrence as current.

     

    in our case we use code in calculated column so it give first row context and  maxx it give second row context and we want to use refrence of  Account column .

     

    when we move row by row in our table first row refer account X and we move second row it still refer X  and we compare it with current acoount column value

    in some case it syntax sugar of variable
    below same code for same result

    Column 2 = 
var a1= 'Table (2)'[Account]
var a = 
    MAXX(
      FILTER(
         'Table (2)',
         'Table (2)'[Account]=a1 && 
         'Table (2)'[Active]="yes"),'Table (2)'[Index]
    )
return
IF('Table (2)'[Index]=a,1,0)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.