Страницы: 1
RSS
PowerPivot: расчёт значений вне диапазона фильтра
 
Добрый день, коллеги.

В 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
 
Так если у вас в модель данных загружены заявки созданные с 05.10., где искать то заявки созданные раньше?
Или вы имеете ввиду, что в модели загружены все заявки, а фильтр стоит в сводной таблице уже, где вы считаете сколько создано заявок за период, и сейчас хотите посчитать сколько за этот период закрыто заявок?
 
StepanWolkoff, верно, в PowerPivot у меня загружены все данные, а в WorkBook настроен фильтр и сводная таблица, где считается Мера.

Когда в WorkBook я применяю фильтр с 05.10 по 07.10, то Заявка 1 отсеивается, хотя необходима для расчёта значения  меры.
 
Не знаю, может я конечно чего не понял, посмотрите файл
 
StepanWolkoff, к сожалению файл повреждён и не открывается. Вы можете его переопубликовать?
 
StepanWolkoff, верно, т.е. в мере "Кол-во закрытых заявок" при выбранном фильтре дат с 05.10 по 07.10 мы должны получить 3
 
Спасибо всем за ответы.

Решить задачу получилось через использование функции 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() мы не можем отфильтровать по любому другому полю. Так же не можем вывести значения в разрезе любого аналитического признака, например, "Оператора".

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

Примечание: что бы открыть файл переименуйте расширение в 7z и разархивируйте его.
Изменено: druzhkov_dv - 09.10.2015 16:57:49
 
Добрый день.

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

Вкратце вся методика решения:
  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.10.2015 16:03:12
Страницы: 1
Наверх