Smile advisory

Сломанная иерархия

By Smile
at 18 October 2020

Рвав-рвав, здесь и сейчас с вами собака Смайл!

Для начала немного о том, почему я грустный:

Санкт-Петебрург, дождь, грязюка, холоднюка.

Приходится носить всякую фигню. Сопротивление бесполезно. Надеюсь, вы тоже мучаетесь, так как вместе - веселее.

Сегодня хотелось бы описать работу с так называемой “сломанной” иерархией данных. В принципе, это довольно часто встречающаяся ситуация, из которой каждый выходит так, как может, но собака Смайл в данной статье постарается разъяснить все доступным языком. Так сказать, “Повторение - мать учения.”

Пример:

В качестве исходных данных поступила информация следующего вида:

– основной набор данных, содержащий ID пользователя, номер документа и сумму - “Dataset”;

– справочник, содержащий информацию о структуре подчинения между сотрудниками - “Staff Hierarhy”.

art_010_screen_1

Задача:

На основе представленных данных реализовать следующие требования:

– построить иерархию данных таким образом, чтобы у каждого сотрудника было одинаковое количество уровней иерархии;

– убрать повторяющиеся значения на разных уровнях иерархии, то есть, произвести ее “схлопывание”.

Построение структурированной иерархии в справочнике “Staff Hierarhy”:

В целях устранения хаоса в исходных данных для начала нам необходимо понять, является ли конкретное значение конечным элементом иерархии. В этом нам может помочь столбец “End Link”.

В принципе, делать его необязательно, но в случае большого объема данных он сможет помочь при проверке конечного результата:

art_010_screen_2

Формула указанного столбца выглядит следующим образом:

End Link = 
CALCULATE (
    COUNTROWS ( 'Staff Hierarhy' ),
    ALL ( 'Staff Hierarhy' ),
    'Staff Hierarhy'[Parent Key] = EARLIER ( 'Staff Hierarhy'[User ID] )
) = 0

Следующим шагом является построение цепочки родительских значений, для последующего определения уровня иерархии. В этом нам поможет столбец “Hierarchy View”, рассчитанный при помощи функции “PATH”:

art_010_screen_3

Формула указанного столбца рассчитывается так:

Hierarchy View = 
PATH ( 'Staff Hierarhy'[User ID], 'Staff Hierarhy'[Parent Key] )

Подсчет уровня иерархии, проиллюстрированнный в столбце “Hierarchy Level”, можно произвести при помощи функции “PATHLENGHT”:

art_010_screen_4

Формула указанного столбца выглядит следующим образом:

Hierarchy Level = 
PATHLENGTH ( 'Staff Hierarhy'[Hierarchy View] )

Выполнив указанные шаги, можно приступать непосредственно к построению уровней иерархии при помощи функций “PATHITEM” и “LOOKUPVALUE”:

art_010_screen_5

Формула столбца для 1-го уровня иерархии - “1st level” представляет собой следующую конструкцию:

1st level = 
LOOKUPVALUE (
    'Staff Hierarhy'[Name],
    'Staff Hierarhy'[User ID], PATHITEM ( 'Staff Hierarhy'[Hierarchy View], 1, INTEGER )
)

Формула столбца для 2-го уровня иерархии - “2nd level” представляет собой следующую конструкцию:

2nd level = 
IF (
    'Staff Hierarhy'[Hierarchy Level] >= 2,
    LOOKUPVALUE (
        'Staff Hierarhy'[Name],
        'Staff Hierarhy'[User ID], PATHITEM ( 'Staff Hierarhy'[Hierarchy View], 2, INTEGER )
    ),
    'Staff Hierarhy'[1st level]
)

Формула столбца для 3-го уровня иерархии - “3rd level” представляет собой следующую конструкцию:

3rd level = 
IF (
    'Staff Hierarhy'[Hierarchy Level] >= 3,
    LOOKUPVALUE (
        'Staff Hierarhy'[Name],
        'Staff Hierarhy'[User ID], PATHITEM ( 'Staff Hierarhy'[Hierarchy View], 3, INTEGER )
    ),
    'Staff Hierarhy'[2nd level]
)

Собственно, 1-я цель по построению автоматическим путем выровненной иерархии на этом закончена, единственное, с чем надо определиться - это с необходимым количеством уровней, поскольку для каждого уровня требуется отдельный расчетный столбец.

Визуализация данных - стандартное поведение иерархии:

Используя стандартный элемент “Matrix” (“Матрица”), мы можем “соединить воедино” данные справочника “Staff Hierarhy” и основного набора данных - таблицы “Dataset”. Визуальный элемент будет выглядеть следующим образом:

art_010_screen_6

При построении матрицы в иерархическом дереве присутствуют дубли в имени сотрудников. Это объяснимое поведение, поскольку “оно так работает”, однако повторяющиеся значения можно убрать, используя “дополнительные приседания”, а именно - соответствующие меры.

Схлопывание иерархии:

Для достижения 2-й поставленной цели нам необходимо рассчитать несколько мер, начиная с определения нижнего уровня вложенности:

Depth Value = 
ISFILTERED ( 'Staff Hierarhy'[1st level] ) + 
ISFILTERED ( 'Staff Hierarhy'[2nd level] ) + 
ISFILTERED ( 'Staff Hierarhy'[3rd level] )

Место каждого сотрудника в иерархическом дереве определяется при помощи следующей конструкции:

Hierarhy Value = 
MAX ( 'Staff Hierarhy'[Hierarchy Level] )

Рассчет суммы по каждому участнику производится по формуле:

Subtotal Amount = 
IF (
    [Depth Value] > [Hierarhy Value] + 1,
    BLANK (),
    IF (
        [Depth Value] = [Hierarhy Value] + 1,
        IF (
            AND (
                VALUES ( 'Staff Hierarhy'[End Link] ) = FALSE,
                SUM ( 'Dataset'[Amount] ) <> 0
            ),
            SUM ( 'Dataset'[Amount] ),
            BLANK ()
        ),
        SUM ( 'Dataset'[Amount] )
    )
)

Устранение дубликатов в иерархическом дереве со сдвигом промежуточных итогов реализуется следующим образом:

Final Amount = 
IF ( [Depth Value] > [Hierarhy Value], BLANK (), SUM ( 'Dataset'[Amount] ) )

Визуализация данных - настроенное поведение иерархии:

Используя стандартный элемент “Matrix” (“Матрица”), и поставив в качестве значения меру “Final Amount” мы можем получить правильный, а также приятный глазу результат:

art_010_screen_7

При этом суммирование по уровню иерархии, так называемые промежуточные итоги, включает в себя как подчиненные элементы, так и свое собственное значение.

Рвав-рвав, наконец-то конец, до новых встречь в Новом году!

Ваш Смайл