Issue getting maximum value of measure

[Brycert]

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:

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.

[Brycert · Accepted solution]

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]

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]

@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])

[Brycert]

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? 

[Brycert · Accepted solution]

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])

[Brycert]

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]

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

[Brycert · Accepted solution]

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!