Forum Discussion

VOx15's avatar
VOx15
Icon for Helper I rankHelper I
6 years ago
Solved

Get min text value for each ID based off another column

Hello, 

 

I have been stuck on this for serveral days and have gotten closed but this dax fomula returns some blanks and it should not. 

I have a data table that has an ID column, Start of month column, Officer name. The ID field repeats because there is an ID for every month. The officer name can change as the months go on. 

 

What i need is a new column that gives me the original officer for each loan. So, far i have this dax:

Original Officer =
VAR MinTime = MIN(StartofMonth)

RETURN
CALCULATE(FIRSTNONBLANK(Officer,1),
FILTER(ALLEXCEPT( Table, ID ), StartofMonth=MinTime),
ID=EARLIER(ID))
 
Here is what the result should look like:
 
 

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    VOx15 This is a bit different if you are going for a measure or a calculated column. Looks like a measure in your case, should be:

    Measure =
  VAR __ID = MAX([ID])
  VAR __MinDate = MINX(FILTER(ALL('Table'),[ID]=__ID),[StartofMonth])
RETURN
  MAXX(FILTER(ALL('Table'),[ID]=__ID) && [StartofMonth]=__MinDate),[Officer])


Column = 
  VAR __ID = [ID]
  VAR __MinDate = MINX(FILTER(ALL('Table'),[ID]=__ID),[StartofMonth])
RETURN
  MAXX(FILTER(ALL('Table'),[ID]=__ID) && [StartofMonth]=__MinDate),[Officer])

    Basically, this is a Lookup Min/Max pattern. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    VOx15 ,

    column =
    var _max = max(filter(Table,[ID] =earlier([ID]) && [DAte] <earlier([Date])),[Date])
    return
    coalesce(max(filter(Table,[ID] =earlier([ID]) && [DAte] =_max),[officer]),[officer])

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    VOx15 This is a bit different if you are going for a measure or a calculated column. Looks like a measure in your case, should be:

    Measure =
  VAR __ID = MAX([ID])
  VAR __MinDate = MINX(FILTER(ALL('Table'),[ID]=__ID),[StartofMonth])
RETURN
  MAXX(FILTER(ALL('Table'),[ID]=__ID) && [StartofMonth]=__MinDate),[Officer])


Column = 
  VAR __ID = [ID]
  VAR __MinDate = MINX(FILTER(ALL('Table'),[ID]=__ID),[StartofMonth])
RETURN
  MAXX(FILTER(ALL('Table'),[ID]=__ID) && [StartofMonth]=__MinDate),[Officer])

    Basically, this is a Lookup Min/Max pattern. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi VOx15 ,

    According to my understand, you want to specify the Officers based on each ID and the earliest date ,right?

     

    You could use the following formula:

    rank =
RANKX (
    FILTER ( ALL ( Table5 ), 'Table5'[ID] = MAX ( 'Table5'[ID] ) ),
    CALCULATE ( MAX ( ( 'Table5'[StartofMonth] ) ) ),
    ,
    ASC
)
    Original =
CALCULATE (
    MAX ( Table5[Officer] ),
    FILTER ( ALL ( Table5 ), 'Table5'[ID] = MAX ( Table5[ID] ) && [rank] = 1 )
)

    My visualization looks like this:

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin