Max value based on category and ID

[Anonymous]

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!

[Anonymous]

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

Thanks.

[Jos_Woolley · Accepted solution]

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]

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!