Forum Discussion
Maximum range below every 1000
try writing it like this
r Maxlndex:=VAR TablelndexPerAP = SUMMARIZE ('Journal Entry', 'Journal Entry'[Reference], "Max Index", MAX ('Journal Entry'[lndex]) ) RETURN MAXX( TOPN( 1, FILTER (TablelndexPerAP, [Max Index] < 1000), [Max Index], DESC), [Max Index])) ==================or======= r Maxlndex:=VAR TablelndexPerAP = SUMMARIZE (ALL('Journal Entry'), 'Journal Entry'[Reference], "Max Index", MAX ('Journal Entry'[lndex]) ) RETURN MAXX( TOPN( 1, FILTER (TablelndexPerAP, [Max Index] < 1000), [Max Index], DESC), [Max Index]))
pls try this
Thank you Ahmedx , for pointing to me in the right direction. As I am doing the journal entry preparation in Excel and not Power BI, I couldn't create the virtual table like in your screen print, so as an alternative measure (no pun intended), I created a measure with a virtual table variable (VAR) like below. My measure output shows the blank value unless I put the reference in the row field. (Reference is a non-numerical field to group the journal index range like shown in my original post). I'd greatly appreciate it if you could let me know how I can produce the measure output without putting any row field to show the maximum number below 1,000 as shown in your example.
Thank you very much for your help.
try writing it like this
r Maxlndex:=VAR TablelndexPerAP = SUMMARIZE ('Journal Entry', 'Journal Entry'[Reference], "Max Index", MAX ('Journal Entry'[lndex]) ) RETURN MAXX( TOPN( 1, FILTER (TablelndexPerAP, [Max Index] < 1000), [Max Index], DESC), [Max Index])) ==================or======= r Maxlndex:=VAR TablelndexPerAP = SUMMARIZE (ALL('Journal Entry'), 'Journal Entry'[Reference], "Max Index", MAX ('Journal Entry'[lndex]) ) RETURN MAXX( TOPN( 1, FILTER (TablelndexPerAP, [Max Index] < 1000), [Max Index], DESC), [Max Index]))
