Hello world, I have a huge list of suppliers that each of them has a specific priority. I want in case of same supplier - different priority to highlight the name of supplier. My idea was something like Max measure = CALCULATE ( MAX ( ' Suppliers'[Priority] ), ALLEXCEPT ( 'Suppliers','Suppliers'[Supplier Name] ) )Min measure = CALCULATE ( Min ( 'Suppliers'[Priority] ), ALLEXCEPT ( 'Suppliers','Suppliers'[Supplier Name] ) ) and then compare the min and max measure, but it says that there's too much data and the visualization has exceeded the available resources. Any idea for this? I attach you a sample table of a single supplier.Supl NameCod1Code 2DateType PriorityGER216100012/31/2011 0:00RAND SUPPLIER98GER217100012/31/2011 0:00RAND SUPPLIER98GER218100012/31/2011 0:00RAND SUPPLIER99GER219100012/31/2011 0:00RAND SUPPLIER99GER220100012/31/2011 0:00RAND SUPPLIER99GER221100012/31/2011 0:00RAND SUPPLIER99GER222100012/31/2011 0:00RAND SUPPLIER99GER223100012/31/2011 0:00RAND SUPPLIER99GER224100012/31/2011 0:00RAND SUPPLIER99GER225100012/31/2011 0:00RAND SUPPLIER99GER226100012/31/2011 0:00RAND SUPPLIER99GER227100012/31/2011 0:00RAND SUPPLIER99GER228100012/31/2011 0:00RAND SUPPLIER99GER229100012/31/2011 0:00RAND SUPPLIER99GER230100012/31/2011 0:00RAND SUPPLIER99Thank you in advance world.!

Anonymous

Not applicable

6 years ago

Solved

Max and Min per Category Calculation Problem

Hello world,

I have a huge list of suppliers that each of them has a specific priority.
I want in case of same supplier - different priority to highlight the name of supplier.
My idea was something like

Max measure = 
    CALCULATE (
        MAX ( ' Suppliers'[Priority] ),
        ALLEXCEPT ( 'Suppliers','Suppliers'[Supplier Name] )
    )

Min measure = 
    CALCULATE (
        Min ( 'Suppliers'[Priority] ),
        ALLEXCEPT ( 'Suppliers','Suppliers'[Supplier Name] )
    )

and then compare the min and max measure, but it says that there's too much data and the visualization has exceeded the available resources. Any idea for this? I attach you a sample table of a single supplier.

Supl Name	Cod1	Code 2	Date	Type	Priority
GER	216	1000	12/31/2011 0:00	RAND SUPPLIER	98
GER	217	1000	12/31/2011 0:00	RAND SUPPLIER	98
GER	218	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	219	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	220	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	221	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	222	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	223	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	224	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	225	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	226	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	227	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	228	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	229	1000	12/31/2011 0:00	RAND SUPPLIER	99
GER	230	1000	12/31/2011 0:00	RAND SUPPLIER	99

Thank you in advance world.!

Anonymous

OK, so you have the output format you want, it's just the measures are overloading the resource limit, right?

Maybe try adding the Min/Max into the table in Power Query beforehand, something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdK7CoAwDIXhV5HOgknqpXUTFBFESsWp+P6v4WWRjvEsCYR825+SmadoSiPc3pOJ6FlSWa6EmAvq30sctrHYjxDW5X33zpzlZzvAOpX1mfX/rRBgGbACWAvYGrANYHVd5VbXVW6BrgToyqq7Oi8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Supl Name" = _t, Cod1 = _t, #"Code 2" = _t, Date = _t, #"Type " = _t, Priority = _t]),
    groupSupplier = Table.Group(Source, {"Supl Name"}, {{"data", each _, type table [Supl Name=text, Cod1=number, Code 2=number, Date=text, #"Type "=text, Priority=number]}}),
    addMinPrio = Table.AddColumn(groupSupplier, "minPriority", each Table.Min([data], "Priority")),
    addMaxPrio = Table.AddColumn(addMinPrio, "maxPriority", each Table.Max([data], "Priority")),
    expandMinPrio = Table.ExpandRecordColumn(addMaxPrio, "minPriority", {"Priority"}, {"minPriority"}),
    expandMaxPrio = Table.ExpandRecordColumn(expandMinPrio, "maxPriority", {"Priority"}, {"maxPriority"}),
    expandData = Table.ExpandTableColumn(expandMaxPrio, "data", {"Cod1", "Code 2", "Date", "Type ", "Priority"}, {"Cod1", "Code 2", "Date", "Type ", "Priority"}),
    chgDateLocaleUS = Table.TransformColumnTypes(expandData, {{"Date", type datetime}}, "en-US"),
    chgAllDataTypes = Table.TransformColumnTypes(chgDateLocaleUS,{{"Cod1", type text}, {"Code 2", type text}, {"Date", type date}, {"Priority", type number}, {"minPriority", type number}, {"maxPriority", type number}})
in
    chgAllDataTypes

I'm thinking this may take some of the calculational load off?

Pete

8 Replies

BA_Pete
Super User
6 years ago
Hi Anonymous

Can you provide an example of what your desired output looks like please?

Pete
Anonymous
Not applicable
6 years ago
It could be due to the number of calculations youre running on at the same time. You might find the below article useful for some troubleshooting ideas. https://blog.crossjoin.co.uk/2020/01/20/visual-has-exceeded-the-available-resources-error-power-bi/

Just another thought have you tried switching measure to calculated collumn does it still cause this problem ?

amitchandak

Super User

6 years ago

Anonymous , During the display of visual can you reduce the number of columns. Also, try to have only on pbix open

Is this min-max static or changes based on filter

Anonymous

Not applicable

6 years ago

https://drive.google.com/file/d/1g_1z_noSZSHvOoi-t4kgle8VOeNUrPOJ/view?usp=sharing

this is kind of the view that the user requested (not the best way to use pbi but that's the current immidiate request)
The min-max per supplier is not exactly static, it might change depending on the code 1 ( this is something like order to the supplier)
Thanks world

BA_Pete

Super User

6 years ago

Anonymous

OK, so you have the output format you want, it's just the measures are overloading the resource limit, right?

Maybe try adding the Min/Max into the table in Power Query beforehand, something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdK7CoAwDIXhV5HOgknqpXUTFBFESsWp+P6v4WWRjvEsCYR825+SmadoSiPc3pOJ6FlSWa6EmAvq30sctrHYjxDW5X33zpzlZzvAOpX1mfX/rRBgGbACWAvYGrANYHVd5VbXVW6BrgToyqq7Oi8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Supl Name" = _t, Cod1 = _t, #"Code 2" = _t, Date = _t, #"Type " = _t, Priority = _t]),
    groupSupplier = Table.Group(Source, {"Supl Name"}, {{"data", each _, type table [Supl Name=text, Cod1=number, Code 2=number, Date=text, #"Type "=text, Priority=number]}}),
    addMinPrio = Table.AddColumn(groupSupplier, "minPriority", each Table.Min([data], "Priority")),
    addMaxPrio = Table.AddColumn(addMinPrio, "maxPriority", each Table.Max([data], "Priority")),
    expandMinPrio = Table.ExpandRecordColumn(addMaxPrio, "minPriority", {"Priority"}, {"minPriority"}),
    expandMaxPrio = Table.ExpandRecordColumn(expandMinPrio, "maxPriority", {"Priority"}, {"maxPriority"}),
    expandData = Table.ExpandTableColumn(expandMaxPrio, "data", {"Cod1", "Code 2", "Date", "Type ", "Priority"}, {"Cod1", "Code 2", "Date", "Type ", "Priority"}),
    chgDateLocaleUS = Table.TransformColumnTypes(expandData, {{"Date", type datetime}}, "en-US"),
    chgAllDataTypes = Table.TransformColumnTypes(chgDateLocaleUS,{{"Cod1", type text}, {"Code 2", type text}, {"Date", type date}, {"Priority", type number}, {"minPriority", type number}, {"maxPriority", type number}})
in
    chgAllDataTypes

I'm thinking this may take some of the calculational load off?

Pete

Forum Discussion

Max and Min per Category Calculation Problem

8 Replies

Related content

Problem with calcul

Calculate Average per category

Calculation problem

calculate max per category.

Calculating cumulative total across categories