Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Функция DAX RELATED() - несколько условий для поиска, Определить нужный столбец в таблице в зависимости от даты события
 
#1
11.08.2016 13:34:43
Здравствуйте!
Пытаюсь освоить надстройки Poverpivot и Power Query.
Как раз подвернулась задача, которую ранее решал через функцию Excel ВПР().
В таблице требовалось найти нужный тариф телефонного разговора.
Сложность в том, что тариф зависит не только от номера телефона, но и от даты звонка, так как оператор связи может менять тарифы несколько раз в месяц.
Поэтому таблица с тарифами состоит из строк с префиксами (первые цифры телефонного номера) и столбцов с датами изменения тарифов.
В PowerPivot связал таблицу с трафиком за месяц с таблицей с тарифами, но найти простое решение поиска нужного тарифа через функцию RELATED() не получилось.

Более подробное описание в файле, там же мои попытки разными способами найти решение.
Хотелось бы решение именно через PowerPivot/Power Query, так как объём реальных данных большой, а планирую в дальнейшем накапливать ежемесячные порции трафика в течение года для анализа и отчётов, что потребует больших объёмов памяти. Пишут, что для Power - надстроек Excel большие данные не представляют трудности.
Изменено: Виктор Косенков - 11.08.2016 13:36:38
 
 
 
#2
11.08.2016 13:45:41
Виктор Косенков, какую версию Excel используете? У меня сейчас перед глазами просто 2010 и я не вижу в файле вашу модель данных, загруженную в PowerPivot.
Поэтому пока совет: Таблицу "Тарифы" обработать в PowerQuery перед добавлением в модель PowerPivot, чтобы привести к нормальному плоскому виду, где Даты смены тарифов были строками, а не столбцами, тогда будет проще фильтровать. ИМХО
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Тарифы"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Prefix", Int64.Type}, {"Направление", type text}, {"01082016", type number}, {"10082016", type number}, {"25082016", type number}, {"02092016", type number}}),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Измененный тип", {"Prefix", "Направление"}, "Атрибут", "Значение"),
    #"Переименованные столбцы" = Table.RenameColumns(#"Другие столбцы с отмененным свертыванием",{{"Атрибут", "Дата"}})
in
    #"Переименованные столбцы"
 
 
 
#3
11.08.2016 13:49:36
Excel 2013
Насчёт плоского вида думал, но тогда префиксы перестанут быть уникальными и не получится связать таблицы в модели (?)
 
 
 
#4
11.08.2016 14:56:25
Смотрите. Добавил в модель три таблицы: Номер_Префикс, Трафик и Тарифы(обработанную в PowerQuery как написал выше). Дальше между Номер_Префикс иТрафик создал связь по полю номер. В таблице Трафик добавил поле Prefix с функцией =RELATED('Номер_Префикс'[Prefix]). Дальше добавляем поле Цена с функцией =CALCULATE(sum('Тарифы'[Цена]),'Тарифы'[Дата]=EARLIEST('Трафик'[Столбец]),'Тарифы'[Prefix]=EARLIER('Трафик'[prefix])).
Вроде правильно работает))
 
 
 
#5
11.08.2016 14:59:14
Спасибо, пойду пробовать.
 
 
 
#6
11.08.2016 15:02:37
Виктор Косенков, по итогам отпишитесь, чтобы было понимание удалось решить или нет.
 
 
 
#7
11.08.2016 16:08:12
StepanWolkoff, Не сразу, но в конце концов заработало!
Заменил запятые в последней формуле на точку с запятой, имена столбцов поправил.
Теперь буду адаптировать к реальным данным.
Ещё раз большое Спасибо!
**********************************
Увы. На реальных данных Excel не хватает памяти.
Трафик за 3 месяца ещё кое как обрабатывает, а на 4-х виснет.
8 Гигабайт памяти на домашнем компьютере.
На рабочем - 4 Гигабайта, пишет ошибку о нехватке памяти уже на одном месяце.
Видимо, слишком много ресурсов требуется для построения виртуальных массивов при вычислении формулы.
Изменено: Виктор Косенков - 22.08.2016 21:59:39 (Описание результата работы на реальных данных)
 
 
 
Страницы: 1
Наверх