Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Обход группировки в PQ с помощью функции DAX
 
#1
27.05.2022 13:38:07
Форумчане, добрый день!

Возникла проблема в ходе работы.
Стоит следующая задача: есть выгрузка по услугам, где также отображены удержания. Нужно проверить качество работы отдела по удержаниям с помощью нескольких метрик.
Есть номер счета, сумма к оплате, сумма удержания. Нужно получить сумму счета по которому было хотя бы 1 удержание, буду называть эту метрику - "Проверено счетов, руб.).
Самостоятельно смог придти к тому варианту, где с помощью еще одного запроса PQ я делал группировку по номеру счета, считал сумму удержаний по счету, с помощью пользовательского столбца принимал все счета, где сумма удержаний > 0 за "проверенный", далее джоинил к основной таблице этот столбец. А дальше уже считал сумму проверенной суммы в power pivot с помощью суммы по условию.
Все было сносно пока я делал модель по 1 месяцу. Как только подключил всю выгрузку (около 10 млн строк), все встало и не хочет загружаться.
Загвоздка в том что есть несколько типов экспертиз и по общему итогу у меня 3 дополнительных запроса для группировок и 3 джоина к основной таблице, думаю это чересчур для такого массива данных.
Изучал тему https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=91528, но к сожалению не нашел решения, но обратил внимание на сообщение Максим Зеленский: "Основной вопрос - зачем заставлять PQ считать формулы, которые потом можно выполнить в DAX (в разы быстрее). Уверен, сам по себе такой столбец вам не нужен, потом все равно будете делить одно на другое и так далее".
Думаю это максимально подходит под мою задачу.
Можете подсказать формулу в PP, чтобы обойти эти доп. запросы с группировками и т.д.? Имею представление об оконных функциях, подзапросах в SQL, но не понимаю как применить эту логику в PP. Заранее спасибо!
Прикладываю примитивную модель моего запроса для наглядности.  
 
 
 
#2
27.05.2022 14:25:10
Алексей Богданов,
1. Зачем делать join? Это очень долгая операция :) Посчитайте это значение группировкой:
Скрытый текст

2. У Вас в справочнике Счетов уже есть признак проверки. Зачем его вообще добавлять в таблицу фактов? Пункт 1 был просто для примера.
Скрытый текст

Мера:
Код
checked sum :=
CALCULATE (
    SUM ( 'Таблица1'[К оплате] );
    'Справочник счетов'[Проверен счет?] = "Да"
)

3. Расчет в PP:
a) Вычисляемый столбец:
Код
check ind = CALCULATE ( SUM ( 'Таблица1'[Согласованое удержание] ) ) > 0

На всякий случай проверьте, может будет шустрее:
Код
check ind_2=
VAR t =
    FILTER ( RELATEDTABLE ( 'Таблица1' ); 'Таблица1'[Согласованое удержание] )
VAR result =
    NOT ISEMPTY ( t )
RETURN
    result

Мера:
Код
checked sum_2:=
CALCULATE (
    SUM ( 'Таблица1'[К оплате] );
    'Справочник счетов'[check ind] = TRUE
)

б) просто мерой без вычисляемого столбца:
Код
checked sum_3 :=
CALCULATE (
    SUM ( 'Таблица1'[К оплате] );
    FILTER (
        'Справочник счетов';
        CALCULATE ( SUM ( 'Таблица1'[Согласованое удержание] ) ) > 0
    )
)
Изменено: surkenny - 27.05.2022 14:25:46
 
 
 
#3
30.05.2022 15:22:26
surkenny, большое спасибо за ответ! Извиняюсь, за несвоевременный ответ.
Попробую разобраться в ваших вариантах, к сожалению, писать запросы сразу в код без использования интерфейса для меня пока сложновато, но думаю опытным путем разберусь.
Каждый из ваших вариантов использует группировку ("справочник счетов" основанный также на группировке), я надеялся, что может быть есть вариант использования только DAX.  
 
 
 
#4
30.05.2022 16:07:07
Цитата
Алексей Богданов написал:
Каждый из ваших вариантов использует группировку ("справочник счетов" основанный также на группировке), я надеялся, что может быть есть вариант использования только DAX.  
1. Вы видели в Power Pivot возможность создавать вычисляемые таблицы?
2. Вы не группировки бойтесь. А Ваших бесконечных join на миллионы строк :).
В любом случае, справочник Счетов откуда возьмется без группировки? Можно не вычислять в PQ, проверен ли счет. Вычисляемый столбец check ind это делает в DAX. Я Вам разные варианты предложил.
 
 
 
#5
30.05.2022 16:36:26
surkenny, именно группировки я и хотел избежать, потому что если даже не джоинить запросы, то все равно время обновления будет гораздо больше, ведь обновлять по 10 млн строк придется уже не один запрос, а несколько.
check ind у вас находится в справочнике Счетов, если его использовать в первоначальном запросе без группировки, то теряется смысл, потому что будут считаться только те услуги, по которым были списания, а услуги в том же счете, но без списаний, не будут входить в список "проверенных".
Но во всяком случае благодаря вам я увидел возможность в вычисляемых столбцах и с помощью доп. формулы смог разметить все услуги в рамках счета которых было хотя бы 1 списание:
Код
=CALCULATE(
SUM([Согласованое удержание]);
FILTER('Таблица1';
[№ рег. счета]=EARLIER([№ рег. счета])))
А далее уже буду использовать как раз логику check ind. Спасибо!
 
 
 
Страницы: 1
Наверх