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 lowe...
  • 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

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!