Post-Image

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

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

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

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

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

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

Пример:

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

– основной набор данных, содержащий 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

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

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

    Ваш Смайл