Max and Min per Category Calculation Problem

[Anonymous]

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.!

[amitchandak]

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

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 · Accepted solution]

@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

[Anonymous]

Seems plausible, but the issue is that unfortunately I can only create measure, and I have no access to the model, as it is on analysis services and I have no privilidges for that. 
I believe that maybe a calculated column on the model could do the trick, but as said before, got no access on that so I'm looking on how to create an effective measure on the view itself.

[v-diye-msft]

Hi @Anonymous 

Not quite understand the requirements, could you please kindly elaborate more? Dummy pbix with expected results will be appreciated.

[Anonymous]

I have uploaded a link with from google drive with the expected result, as it should look also in the pbix file. please check this.
https://drive.google.com/file/d/1g_1z_noSZSHvOoi-t4kgle8VOeNUrPOJ/view?usp=sharing
Thanks in advance.!

[Anonymous]

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 ?

[BA_Pete]

Hi @Anonymous 

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

Pete