Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Max value based on category and ID

Hi all,

 

I'm looking for the ID of a higher level in the hiearichy.

 

The level hierarchy is:

1

2

3

 

E.g a Level 3 with ID 5 should fint the highest ID for the Level 2 total and still lower than it's own ID.

 

This is an example of the wanted structure:

 

 

Thanks!

 

  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Hi,

    This measure:

     

    ID of Parent =
VAR ThisLevel =
    MIN( 'Table'[Level] )
VAR ThisID =
    MIN( 'Table'[ID] )
VAR ParentID =
    CALCULATE(
        MAX( 'Table'[Description] ),
        FILTER(
            ALL( 'Table' ),
            'Table'[Level] = ThisLevel - 1
                && 'Table'[ID] < ThisID
        )
    )
RETURN
    ParentID

     

    In the Visualizations panel, click the dropdown next to 'ID' and check 'Show Items with no data' if you want the result for the first ID to be included.

    Regards

3 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi,

    This measure:

     

    ID of Parent =
VAR ThisLevel =
    MIN( 'Table'[Level] )
VAR ThisID =
    MIN( 'Table'[ID] )
VAR ParentID =
    CALCULATE(
        MAX( 'Table'[Description] ),
        FILTER(
            ALL( 'Table' ),
            'Table'[Level] = ThisLevel - 1
                && 'Table'[ID] < ThisID
        )
    )
RETURN
    ParentID

     

    In the Visualizations panel, click the dropdown next to 'ID' and check 'Show Items with no data' if you want the result for the first ID to be included.

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Jos_Woolley ,

       

      Thanks for your suggestion! It worked just like I wanted it.

       

      My appologies for the late response. I got caught on other issues.

       

      Thanks again!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Is there any way to create is as a column instead of a measure?

     

    Thanks.