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,
First post and I actually got problems even formulating the subject.
Anyway - this is what I want to achieve, and apparently my skills are not sufficient to figure out the solution.
In my head I want to to something like this:
For each ID -> Find Max sequence -> Return Value in same row
ID | Sequence | Value |
1000 | 1 | 30 |
1000 | 2 | 50 |
1000 | 3 | 10 |
1001 | 1 | 30 |
1001 | 2 | 70 |
So in this case I want to extract
ID 1000 - Value 10
ID 1001 - Value 70
I got a feeling this could be easy, but I cannot find the correct method. I always end up with sum of all Values per ID.
Answers highly appreciated.
Solved! Go to Solution.
Hi @Anonymous
Try this, where Table1 is the name of your table:
1. Set Table1[ID] in the rows of a matrix visual
2. Place this measure in values of the matrix:
ValMaxSequence = CALCULATE ( VALUES ( Table1[Value] ), FILTER ( ALL ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) ) )
Thank you all,
I had the same issue except that the maximum value to be retrieved is from another table. yet it worked well..
now i have an issue as I want to create a visual where the value of the new measure can be aggregated to find out the total... but it is not working ( I only get to see one value) and there is no optoin to aggregate can you please help thanks
Hi @Anonymous
Try this, where Table1 is the name of your table:
1. Set Table1[ID] in the rows of a matrix visual
2. Place this measure in values of the matrix:
ValMaxSequence = CALCULATE ( VALUES ( Table1[Value] ), FILTER ( ALL ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) ) )
Hi, I expected receive both values with this formula , for ID 1000 and 1001, however I get only the max for 1000 (the formula get the absolute max of Sequence). How can I get both max? thanks in advance! (this measure was created in powerpivot in Excel and I know could be a little different in PowerBI)
Thank you, that solved it!
I do however struggle with figuring out why this works..
I have to work on this one. Thank you again!
@Anonymous
Let's run an example of how the measure works.
Take the row in your matrix visual with ID =1000. In that row you have an active filter on Table1[ID]=1000. We want to select the row in Table1 that has the max sequence and that is why we use:
FILTER ( ALL ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) )
Note that MAX ( Table1[Sequence] ) is affected by the filter context and thus will return the MAX sequence for ID=1000, i.e. 3.
The result of filter FILTER( ), which is now the row in Table1 with ID=1000 and Sequence=3, is then applied to the first parameter in CALCULATE : VALUES( Table1[Value]). This returns the value in column Table1[Value] in the filtered row of Table1, i.e. 10
@Anonymous
Legitimate question. ALL () in this case is actually not necessary. It would work also with
FILTER ( VALUES ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) )
and even with
FILTER ( Table1, Table1[Sequence] = MAX ( Table1[Sequence] ) )
because in all cases we're modifying the filter on Table1[Sequence] but leaving the one on Table1[ID] unaltered
The behavior of ALL( ) is a bit tricky. It can work in two ways that might sound like the same but certainly ain't:
a) as a filter remover or
b) as a function that just returns a table without filters applied (but without removing filters elsewhere).
The latter is what happens in this case. Have a look at this excellent article by the Italian gurus.
@Anonymous
good that you ask cause I just realized that the code has a problem. It works in this case because in your sample table the max Sequence is different for the two IDs. If it were the same for both, the code would generate an error at the grand total row (no filter on Table1[ID] there). We'd need to update it like this:
ValMaxSequence = CALCULATE ( SELECTEDVALUE ( Table1[Value] ), FILTER ( ALL ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) ) )
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |