topic Re: Issue getting maximum value of measure in DAX Commands and Tips

Issue getting maximum value of measure

Brycert — Wed, 05 May 2021 13:44:54 GMT

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.

Re: Issue getting maximum value of measure

amitchandak — Wed, 05 May 2021 13:58:46 GMT

Brycert , Refer if these two can help

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=6cd37af6-e634-4497-afb8-20e7e9ddaf26&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

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

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

Re: Issue getting maximum value of measure

Brycert — Wed, 05 May 2021 14:13:24 GMT

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?

Re: Issue getting maximum value of measure

Brycert — Wed, 05 May 2021 14:17:30 GMT

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:

Re: Issue getting maximum value of measure

Brycert — Wed, 05 May 2021 14:18:13 GMT

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

Re: Issue getting maximum value of measure

Brycert — Wed, 05 May 2021 14:27:20 GMT

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!

Re: Issue getting maximum value of measure

Brycert — Wed, 05 May 2021 19:23:09 GMT

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

Re: Issue getting maximum value of measure

Brycert — Tue, 25 May 2021 14:57:48 GMT

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