Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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')
)
Hi @Anonymous
it looks like you need MINX() function like
Measure =
CALCULATE(
MINX('Table',
CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Class]))),
ALL('Table')
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
65 | |
50 | |
45 | |
20 | |
17 |