Measure with a variable

[Anonymous]

Hi,

I am trying to upgrade a company report such that it is future proof. I am currently facing a seemingly simple problem which has been bugging me for the past 2 days. I can't share the data but I'll try to sketch the scenario as well as possible.

My data consists of many rows and columns, which are divided according to the following example:

table 1

	Class	Value	Slicer columns
1	A	1	Fullfills requirements
2	A	2	Fullfills requirements
3	A	3	Bad solution
4	B	4	Fullfills requirements
5	B	5	Bad solution
6	C	6	Fullfills requirements
7	C	7	Bad solution

Users give input in the various columns, which results in a solution meeting the requirements or not (here sumarized in a column, in the real report, this is done through slicers in the project). I want to find the maximum of the valid solutions per class, but at the same time I want to find the minimum of all the maximums (which is my most conservative solution to the problem). e.g. The maximum acceptable values are (2, 4 and 6), so my measure should output 2, which is the minimum of the 3 maximums.

My measure which does this, is as follows (I left the good/bad solution filter out for simplicity):

measure =

VAR maxA = CALCULATE(MAX(table1[Value]); table1[Class] = "A")

VAR maxB = CALCULATE(MAX(table1[Value]); table1[Class] = "B")

VAR maxC = CALCULATE(MAX(table1[Value]); table1[Class] = "C")

RETURN MIN(MIN(maxA;maxB) ; maxC)

This measure does exactly as expected. However, the amount of classes in my data can change in the future. Hence, I would like to include a loop over a variable amount of classes instead of hard coding them.

The slicer criteria are set inside the report. Therefore, I cannot use the query editor, as that does all the calculations while loading the data, therefore ignoring future slicers (at least to my understanding, correct me if I am wrong). I think I am limited to using a measure, but those don't work well with variables/functions.

I hope someone can help me out.

Cheers,

Jeffrey

[az38 · Accepted solution]

Hi @Anonymous 

it looks like you need MINX() function like

Measure = 
CALCULATE(
MINX('Table',
CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Class]))),
ALL('Table')
)

[Anonymous · Accepted solution]

Here's the full and correct solution:

https://1drv.ms/u/s!ApyQEauTSLtOgYM665_GXHuUZylVyw?e=IQUTF8

Best
D

[az38 · Accepted solution]

Hi @Anonymous 

it looks like you need MINX() function like

Measure = 
CALCULATE(
MINX('Table',
CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Class]))),
ALL('Table')
)