Forum Discussion
VOx15
Helper I
Helper IGet 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...
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
Greg_Deckler
Community Champion
Community ChampionVOx15 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
VOx15Helper I
Helper IGreg_Deckler thank you! Worked great!