none
PowerPivot: расчёт значений вне диапазона фильтра RRS feed

  • Вопрос

  • Добрый день, коллеги.

     

    В PowerPivot есть таблица, сформированная из списка SharePoint:

    ИД

    Имя

    Дата создания

    Дата закрытия

    Вес

    1

    Заявка 1

    04.10.2015

    05.10.2015

    1

    2

    Заявка 2

    05.10.2015

    06.10.2015

    3

    3

    Заявка 3

    06.10.2015

    07.10.2015

    4

    4

    Заявка 4

    07.10.2015

    08.10.2015

    3

    5

    Заявка 5

    08.10.2015

    09.10.2015

    2

     

    В WorkBook настроен фильтр по "Дате создания", к примеру, он задан с 05.10 по 07.10.

    Мы хотим получить количество заявок, "Дата закрытия" которых попадает в установленный диапазон (05.10 <= Дата закрытия >= 07.10).

     

    Но при текущем фильтре Заявка ID=1 отсеется, и мы получим только Заявку 2 и 3.

     

    Вопрос в том, как нам получить ещё и Заявку 1?

     

    Пробовали такие варианты:

    Если в PowerPivot применить формулу:

    CALCULATE(COUNT([Идентефикатор]);FILTER('Кредиты';'Кредиты'[ДатаЗакрытия]>=MIN('Кредиты'[ДатаСоздания])&&'Кредиты'[ДатаЗакрытия]<=MAX('Кредиты'[ДатаСоздания])))

    То, формула возвращает только заявки с "Датой создания" за отфильтрованный период.

     

    Если в этой формуле использовать ALL()  (она передаёт всю таблицу без ранее установленных фильтров): CALCULATE(COUNT([Идентефикатор]);FILTER(ALL('Кредиты')…,

    То, в этом случае данные берутся без фильтров вообще, т.е. мы получаем весь набор данных.

    keywords: PowerPivot, OLAP Tabular mode,  SharePoint, PowerPivot Excel, DAX, MDX

    Версии: Excel 2013, SQL 2012R2 SP1

    7 октября 2015 г. 11:13

Ответы

  • Добрый день.

     

    Задачу удалось решить через отдельный справочник дат.

     

    Вкратце вся методика решения:

    1. В новом WorkBook создаём источник "Из веб-канала данных ODATA"
      1. Для удобства работы можно создать отдельный источник данных для основного списка "Кредиты" и для справочников
      2. Пример строки подключения для списка "Кредиты":
        1. http://URL портала/_vti_bin/listdata.svc/Кредиты?$filter=ContentType ne 'Folder'&$top=5000&$orderby=ДатаСоздания desc
      3. При создании источников данных выбираем "Создание таблицы", после чего она автоматически добавляются в WorkBook и в PowerPivot как источник данных
      4. Плюс этого решения в том, что при необходимости изменить строку подключения, открываем файл источника данных (расположение смотри в WorkBook Данные - Существующие подключения) и изменяем в текстовом редакторе. После чего данные сразу можно обновить как в WorkBook, так и в PowerPivot
    2. Справочник "Календарь" был добавлен вручную и также вручную был добавлен в PP как источник данных
    3. В PowerPivot добавили связи "ДатаСоздания" - "Datekey" и "ДатаЗакрытия" - "Datekey"
    4. Все зависимые от фильтра дат формулы переделали по следующему шаблону:
      1. Было
        1. CALCULATE(COUNTROWS(['Кредиты']);FILTER(ALL('Кредиты');DATEVALUE('Кредиты'[ДатаЗакрытия])>=DATEVALUE(MIN('Кредиты'[ДатаСоздания]))&&DATEVALUE('Кредиты'[ДатаЗакрытия])<=DATEVALUE(MAX('Кредиты'[ДатаСоздания]))))
      2. Стало
        1. CALCULATE(COUNTROWS('Кредиты');USERELATIONSHIP('Кредиты'[ДатаСоздания];'Календарь'[Datekey]))
    5. Сводную таблицу сделали так:
      1. В " значения" вывели меры
      2. В "Строки" вывели " значения"
      3. В "Колонны" вывели "Datekey"
      4. Фильтр дат сделали по "Datekey"

     

    В итоге формулы, зависимые от фильтра дат по "ДатеСоздания" и по "ДатеЗакрытия" считаются верно и не мешают друг другу.


    • Помечено в качестве ответа Druzhkov_dv 14 октября 2015 г. 12:50
    • Изменено Druzhkov_dv 14 октября 2015 г. 12:54
    14 октября 2015 г. 12:50

Все ответы


  • Решить задачу получилось через использование функции ALL().

    Нашей ошибкой было 2 причины:
    1. Не правильный синтаксис формул

    Было:
    COUNTX(FILTER(ALL(Table)))
    Правильно:
    CALCULATE(COUNT();FILTER(ALL(Table);FilterExpression))

    2. Сравнение дат необходимо выполнять через DATAVALUE, т.к. дата содержит часы и минуты

    Конечная формула выглядит так:
    CALCULATE(COUNTROWS(['Кредиты']);FILTER(ALL('Кредиты');DATEVALUE('Кредиты'[ДатаЗакрытия])>=DATEVALUE(MIN('Кредиты'[ДатаСоздания]))&&DATEVALUE('Кредиты'[ДатаЗакрытия])<=DATEVALUE(MAX('Кредиты'[ДатаСоздания]))))

    Но она содержит потенциальную ошибку: если применять фильтр не по диапазонам "от до", а по конкретным датам, например, 06 и 08,без 07.10.2015, то из-за MIN и MAX 07 тоже будет учитываться, что не верно. Поэтому в данном случае вариант с использованием справочника дат будет более предпочтительным.

    Но появился другой вопрос: при использовании в формуле ALL() мы не можем отфильтровать по любому другому полю. Так же не можем вывести значения в разрезе любого аналитического признака, например, "Оператора".

    Вопрос: как добиться ожидаемого результата? Мы догадываемся, что это из-за использования функции ALL(Table), которая заставляет игнорировать все фильтры. Но при использовании функции ALL('Table'[Дата Создания]), т.е. чисто по колонке, получаем ошибку.
    9 октября 2015 г. 13:59
  • Таблица данных:

    ИД Имя Дата создания Дата закрытия Вес Оператор
    1 Заявка 1 04.10.2015 05.10.2015 1 Оператор 1
    2 Заявка 2 05.10.2015 06.10.2015 2 Оператор 2
    3 Заявка 3 05.10.2015 07.10.2015 3 Оператор 2
    4 Заявка 4 06.10.2015 07.10.2015 4 Оператор 3
    5 Заявка 5 06.10.2015 09.10.2015 5 Оператор 1
    6 Заявка 6 07.10.2015 07.10.2015 6 Оператор 2
    7 Заявка 7 07.10.2015 08.10.2015 7 Оператор 3
    8 Заявка 8 08.10.2015 09.10.2015 8 Оператор 3
    9 Заявка 9 08.10.2015 09.10.2015 9 Оператор 2
    Пример таблицы данных с фильтром по Дате закрытия с 07 по 08.10.2015
    ИД Имя Дата создания Дата закрытия Вес Оператор
    3 Заявка 3 05.10.2015 07.10.2015 3 Оператор 2
    4 Заявка 4 06.10.2015 07.10.2015 4 Оператор 3
    6 Заявка 6 07.10.2015 07.10.2015 6 Оператор 2
    7 Заявка 7 07.10.2015 08.10.2015 7 Оператор 3





























    Ожидаемый результат

    Ожидаемый результат Сумма Весов по закрытым заявкам
    Оператор 2 9
    Оператор 3 11
    Общий итог 20

    Фактический результат

    Фактический результат Сумма Весов по закрытым заявкам
    Оператор 2 20
    Оператор 3 20
    Общий итог 20















    • Изменено Druzhkov_dv 9 октября 2015 г. 14:12
    9 октября 2015 г. 14:11
  • Добрый день.

     

    Задачу удалось решить через отдельный справочник дат.

     

    Вкратце вся методика решения:

    1. В новом WorkBook создаём источник "Из веб-канала данных ODATA"
      1. Для удобства работы можно создать отдельный источник данных для основного списка "Кредиты" и для справочников
      2. Пример строки подключения для списка "Кредиты":
        1. http://URL портала/_vti_bin/listdata.svc/Кредиты?$filter=ContentType ne 'Folder'&$top=5000&$orderby=ДатаСоздания desc
      3. При создании источников данных выбираем "Создание таблицы", после чего она автоматически добавляются в WorkBook и в PowerPivot как источник данных
      4. Плюс этого решения в том, что при необходимости изменить строку подключения, открываем файл источника данных (расположение смотри в WorkBook Данные - Существующие подключения) и изменяем в текстовом редакторе. После чего данные сразу можно обновить как в WorkBook, так и в PowerPivot
    2. Справочник "Календарь" был добавлен вручную и также вручную был добавлен в PP как источник данных
    3. В PowerPivot добавили связи "ДатаСоздания" - "Datekey" и "ДатаЗакрытия" - "Datekey"
    4. Все зависимые от фильтра дат формулы переделали по следующему шаблону:
      1. Было
        1. CALCULATE(COUNTROWS(['Кредиты']);FILTER(ALL('Кредиты');DATEVALUE('Кредиты'[ДатаЗакрытия])>=DATEVALUE(MIN('Кредиты'[ДатаСоздания]))&&DATEVALUE('Кредиты'[ДатаЗакрытия])<=DATEVALUE(MAX('Кредиты'[ДатаСоздания]))))
      2. Стало
        1. CALCULATE(COUNTROWS('Кредиты');USERELATIONSHIP('Кредиты'[ДатаСоздания];'Календарь'[Datekey]))
    5. Сводную таблицу сделали так:
      1. В " значения" вывели меры
      2. В "Строки" вывели " значения"
      3. В "Колонны" вывели "Datekey"
      4. Фильтр дат сделали по "Datekey"

     

    В итоге формулы, зависимые от фильтра дат по "ДатеСоздания" и по "ДатеЗакрытия" считаются верно и не мешают друг другу.


    • Помечено в качестве ответа Druzhkov_dv 14 октября 2015 г. 12:50
    • Изменено Druzhkov_dv 14 октября 2015 г. 12:54
    14 октября 2015 г. 12:50