Dynamic MAX for all rows

[Anonymous]

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)

[DataInsights]

@Anonymous, is there one slicer for each column (H1, H2, etc.), or is there one slicer that filters multiple columns?

[Anonymous]

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

You can replicate this logic for each column's slicer (H1, H2, etc.).

[Anonymous]

@DataInsights I need the max to be just one column, not 5 different ones 😕 

[DataInsights]

@Anonymous, revised measure:

Max Layer = 
CALCULATE ( MAX ( Layers[# Layers] ), ALLSELECTED ( Layers ) )