Forum Discussion
Dynamic MAX for all rows
I have a table that looks like this:
| H1 | H2 | H3 | H4 | H5 | # Layers |
| John Smith | 1 | ||||
| John Smith | Mary Jane | 2 | |||
| John Smith | Mary Jane | Andrew Johnson | 3 | ||
| John Smith | Mary Jane | Andrew Johnson | Ariel Parker | 4 | |
| John Smith | Mary Jane | Andrew Johnson | Mark Williams | 4 | |
| John Smith | Mary Jane | Jackson Mills | 3 | ||
| John Smith | Mary Jane | Jackson Mills | Justin Hunt | 4 | |
| John Smith | Mary Jane | Jackson Mills | Justin Hunt | Janet Lind | 5 |
I want to add a column that shows the Max value of the Layers column among all the rows.
So for the way the table is right now, all the rows in the max column should be 5.
But I want the max to change acoording to the filter. So if I filter Andrew Johnson in H3, the max column should be 4.
The user can filter any of those columns (H1, H2, H3, H4 or H5)
5 Replies
- DataInsights
Super User
Anonymous, is there one slicer for each column (H1, H2, etc.), or is there one slicer that filters multiple columns?
DataInsights There is one slicer for each column. Depending on who the user is, they are going to filter on H1, or H2, etc. So if I'm Andrew, for example, I'm gonna use the H3 slicer because my name is in the 3rd hierarchy
- DataInsights
Anonymous, try this measure:
Max Layer H3 = VAR vSelName = SELECTEDVALUE ( Layers[H3] ) VAR vMaxLayer = CALCULATE ( MAX ( Layers[# Layers] ), ALL ( Layers ), Layers[H3] = vSelName ) RETURN vMaxLayerYou can replicate this logic for each column's slicer (H1, H2, etc.).
