Forum Discussion
Maximum range below every 1000
Hello Power BI community,
I've writte a measure below which produced the range of journal entry row numbers as shown in the 2nd screen shot. Further to this, I'd like to identify the maximum number (in the case below 965 which is the maximum before reaching 1000, as journal entry uploading sheet can only take up to 1000 lines of bookeeeping lines per uploading. I am currently identifying manually what is the maximum number of journal entry line, but I'd like to automate this quick task by writing an appropriate measure. Journal entries can be up to several thousand lines and each uploading sheet can take no more than 1000 lines. Thank you 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]))
5 Replies
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]))
Hi,
I cannot understand your question. Share data in a format that can be pasted in an M Excel file, explain the question and show the expected result.
Thank you Ahmedx ! It worked perfectly.
