cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brycert
New Member

Issue getting maximum value of measure

Hello everyone, 

 

I am brand new to this forum so forgive me if I missed something. Here is my issue:

 

Within power bi I have formed a report which is simply (initially) a count of claims per Pharmacy Chain. The report looks like this:

 

Brycert_0-1620221891511.png

I sorted the list intentionally by "Count_of_Claims_Paid" (a measure) to underscore my issues. Now all I want to do is to retreive the maximum value of this measure in order to compare each ChainName against the that maximum value. I have tried literally everything from summarized tables, various groupings all to no avail as quite often the maximum Im finding is simply the count_of_claims for that same rows which is obviously incorrect. I simply want to retreive the value (in the context of the posted image which is sliced by month and year) which in this current context is 520736. The only simply way I could find was this:

 

Max_Count_Of_Claims_Paid =
MAXX(ALL(Claim_partition_v1),Claim_partition_v1[Count_Of_Claims_Paid])
 
But this leads to the error "Insufficient resource available.." in Power bi desktop and when I try to publish it to the power bi service, I get this error:
 
Resource Governing: The memory used by the query exceeded the configured limit. The query or calculations referenced by it might be too memory intensive. Please consider simplifying the query or calculations. If the dataset is hosted on a dedicated capacity/server, you may also reach out to your capacity/server administrator to see if the per-query memory limit can be increased. Additional information: Requested 3541300KB, Limit 3355443KB. Technical Details: RootActivityId:
 
Is there no simple way to retreive this value othen than scanning the entire Claim_partition_v1 table? I simply want the maximum per year/month filter.
2 ACCEPTED SOLUTIONS

Hi Amit, I actually tried your second formula:

 

Max_Count_Of_Claims_Paid =
MAXX(Summarize((ALL(Claim_partition_v1)), Provider[ChainName],Claim_LastUpdate[MonthName],Claim_LastUpdate[year],"_1",Claim_partition_v1[Count_Of_Claims_Paid]), [_1]) and it works!! Thank so much with my compliments!
 
Brycert_0-1620224833683.png

 

View solution in original post

Correction folks the actual answer is this:

 

Max_Count_Of_Claims_Paid =
MAXX(Summarize(Claim_partition_v1, Provider[ChainName],Claim_LastUpdate[MonthName],Claim_LastUpdate[year],"_1",Claim_partition_v1[Count_Of_Claims_Paid]), [_1])
 

View solution in original post

7 REPLIES 7
Brycert
New Member

Hi everyone,

 

I think the most accurate solution is the following:

 

Max_Count_Of_Claims_Paid: = VAR ValuesDisplayed =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Claim,Provider[ChainName],Claim_LastUpdate[year],Claim_LastUpdate[MonthName]),"@Claim_Count", Claim[Count_Of_Claims_Paid]),
ALLSELECTED()
)

VAR MAXVAL=MAXX(ValuesDisplayed,[@Claim_Count])
return MAXVAL

amitchandak
Super User IV
Super User IV

@Brycert , Refer if these two can help

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=6cd37af6-...

https://windowsreport.com/memory-error-allocation-failure/

 

 

Check formula like this

 

 

Max_Count_Of_Claims_Paid =
MAXX(Summarize((Claim_partition_v1), Claim_partition_v1[chain_name],Claim_partition_v1[Month name],Claim_partition_v1[year],"_1",Claim_partition_v1[Count_Of_Claims_Paid]), [_1])

 

or

 

Max_Count_Of_Claims_Paid =
MAXX(Summarize(All(Claim_partition_v1), Claim_partition_v1[chain_name],Claim_partition_v1[Month name],Claim_partition_v1[year],"_1",Claim_partition_v1[Count_Of_Claims_Paid]), [_1])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you folr your prompt reply. I should have said that the chain name is coming from a table called "Provider" and the "Month" and "year" columns are coming from a "Datedim" table. How would the calculation be changed knowing this? 

Correction folks the actual answer is this:

 

Max_Count_Of_Claims_Paid =
MAXX(Summarize(Claim_partition_v1, Provider[ChainName],Claim_LastUpdate[MonthName],Claim_LastUpdate[year],"_1",Claim_partition_v1[Count_Of_Claims_Paid]), [_1])
 

View solution in original post

Hi Amit, changing the measure to reflect the correct table names, namely this (per your suggestion) i.e.

Max_Count_Of_Claims_Paid =
MAXX(Summarize((Claim_partition_v1), Provider[ChainName],Claim_LastUpdate[MonthName],Claim_LastUpdate[year],"_1",Claim_partition_v1[Count_Of_Claims_Paid]), [_1]) yields this:

 

Brycert_0-1620224229641.png

 

So the grand total is correct, just not the individual rows.

Hi Amit, I actually tried your second formula:

 

Max_Count_Of_Claims_Paid =
MAXX(Summarize((ALL(Claim_partition_v1)), Provider[ChainName],Claim_LastUpdate[MonthName],Claim_LastUpdate[year],"_1",Claim_partition_v1[Count_Of_Claims_Paid]), [_1]) and it works!! Thank so much with my compliments!
 
Brycert_0-1620224833683.png

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors