Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to Solution.
@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
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
@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
@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])
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |