Excel DAX — Работа с текстом и датами

DAX может использоваться в сценариях, включающих работу с текстом, извлечение и составление значений даты и времени или создание значений на основе условия. Вы можете сделать следующее с DAX —

• Создайте ключевой столбец в таблице путем конкатенации.
• Составьте дату на основе частей даты, извлеченных из текстовой даты.
• Определите пользовательский формат даты.
• Изменить типы данных с помощью формулы.
  • Преобразуйте действительные числа в целые числа.
  • Преобразуйте действительные числа, целые числа или даты в строки.
  • Преобразуйте строки в реальные числа или даты.

Создание ключевого столбца путем конкатенации

Модель данных в PowerPivot допускает только один ключевой столбец. Он не поддерживает составные ключи, которые вы можете найти во внешних источниках данных. Следовательно, если какие-либо составные ключи существуют в таблице в источнике данных, вам необходимо объединить их в один ключевой столбец для таблицы в модели данных.

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

= CONCATENATE ([Column1], [Column2]) 

Функция DAX CONCATENATE принимает только два аргумента. Если какой-либо из аргументов не является текстовым типом данных, он будет преобразован в текстовый. Функция DAX CONCATENATE возвращает объединенную строку.

Дата, основанная на частях даты, извлеченных из текстовой даты

Модель данных в Power Pivot поддерживает тип данных datetime для значений даты и времени. Функции DAX, которые работают со значениями даты и / или времени, требуют тип данных datetime для аргументов.

Если ваш источник данных содержит даты другого формата, вам необходимо сначала извлечь части даты, используя формулу DAX, и объединить эти части, чтобы получить действительный тип данных DAX datetime.

Вы можете использовать следующие функции DAX для извлечения и составления дат —

ДАТА — возвращает указанную дату в формате datetime.

DATEVALUE — конвертирует дату в виде текста в дату в формате datetime.

TIMEVALUE — Преобразует время в текстовом формате в время в формате datetime.

Определение пользовательского формата даты

Предположим, что даты в вашем источнике данных не представлены в стандартном формате. Вы можете определить пользовательский формат даты, чтобы гарантировать, что значения обрабатываются правильно. Функция DAX FORMAT позволяет преобразовать значение в текст в соответствии с указанным форматом.

FORMAT (<value>, <format_string>) 

Функция FORMAT возвращает строку, содержащую значение, отформатированное в соответствии с определением format_string.

Вы можете использовать предварительно определенные форматы даты и времени или создать пользовательские форматы даты и времени для аргумента format_string функции FORMAT.

Ниже приведены предварительно заданные имена форматов даты и времени. Если вы используете строки, отличные от этих предопределенных строк, они будут интерпретироваться как пользовательский формат даты и времени.

С. Нет.	Format_String & Description
1	«Общая дата» Отображает дату и / или время. Например, 10.02.2015, 10:10:32
2	«Длинная дата» или «Средняя дата» Отображает дату в соответствии с длинным форматом даты. Например, среда, 7 марта 2016 г.
3	«Короткое свидание» Отображает дату, используя краткий формат даты. Например, 2/03/2016
4	«Много времени» Отображает время, используя длительный формат времени. Обычно включает часы, минуты и секунды. Например, 10:10:32
5	«Среднее время» Отображает время в 12-часовом формате. Например, 9:30 вечера
6	«Короткое время» Отображает время в 24-часовом формате. Например, 14:15

«Общая дата»

Отображает дату и / или время. Например, 10.02.2015, 10:10:32

«Длинная дата» или «Средняя дата»

Отображает дату в соответствии с длинным форматом даты. Например, среда, 7 марта 2016 г.

«Короткое свидание»

Отображает дату, используя краткий формат даты. Например, 2/03/2016

«Много времени»

Отображает время, используя длительный формат времени.

Обычно включает часы, минуты и секунды.

Например, 10:10:32

«Среднее время»

Отображает время в 12-часовом формате.

Например, 9:30 вечера

«Короткое время»

Отображает время в 24-часовом формате.

Например, 14:15

Кроме того, вы можете использовать символы в следующей таблице для создания пользовательских форматов даты и времени.

С. Нет.	Характер и описание
1	: Разделитель времени Разделитель времени Разделяет часы, минуты и секунды при форматировании значений времени.
2	/ Разделитель даты. Разделяет день, месяц и год форматирования значений даты.
3	% Используется для обозначения того, что следующий символ должен читаться как однобуквенный формат безотносительно к последним буквам Также используется для указания того, что однобуквенный формат читается как определенный пользователем формат.

:

Разделитель времени

Разделитель времени Разделяет часы, минуты и секунды при форматировании значений времени.

/

Разделитель даты.

Разделяет день, месяц и год форматирования значений даты.

%

Используется для обозначения того, что следующий символ должен читаться как однобуквенный формат безотносительно к последним буквам Также используется для указания того, что однобуквенный формат читается как определенный пользователем формат.

Ниже приведены данные различных персонажей.

• % d — отображает день в виде числа без начального нуля (например, 5).

• % dd — отображает день в виде числа с начальным нулем (например, 05).

• % ddd — отображает день в виде сокращения (например, вс).

• % dddd — отображает день как полное имя (например, воскресенье).

• % M — отображает месяц в виде числа без начального нуля (например, январь представлен как 1).

• % MM — отображает месяц в виде числа с начальным нулем (например, январь представлен как 01).

• % MMM — отображает месяц как сокращение (например, январь представлен как январь).

• % MMMM — отображает месяц как полное название месяца (например, январь).

• % gg — отображает строку периода / эры (например, AD).

• % h — Отображение часа в виде числа без начальных нулей с использованием 12-часовых часов (например, 1:15:15 PM). Используйте % h, если это единственный символ в вашем пользовательском числовом формате.

• % чч — отображает час в виде числа с ведущими нулями, используя 12-часовые часы (например, 01:15:15 PM).

• % H — Отображение часа в виде числа без начальных нулей с использованием 24-часовых часов (например, 13:15:15, 1:15:15). Используйте% H, если это единственный символ в вашем пользовательском числовом формате.

• % ЧЧ — отображает часы в виде числа с ведущими нулями, используя 24-часовые часы (например, 13:15:15, 1:15:15).

• % m — отображает минуты в виде числа без начальных нулей (например, 2: 1: 15). Используйте% m, если это единственный символ в вашем пользовательском числовом формате.

• % mm — отображает минуты в виде числа с ведущими нулями (например, 2:01:15).

• % s — отображает секунду как число без начальных нулей (например, 2: 15: 5). Используйте% s, если это единственный символ в вашем пользовательском числовом формате.

• % ss — отображает секунду как число с ведущими нулями (например, 2:15:05).

• % f — отображает доли секунд. Например, ff отображает сотые доли секунды, тогда как ffff отображает десятитысячные доли секунды. Вы можете использовать до семи символов f в своем пользовательском формате. Используйте % f, если это единственный символ в вашем пользовательском числовом формате.

• % t — использует 12-часовые часы и отображает заглавную букву A в течение любого часа до полудня; отображает заглавную букву P в течение любого часа между полуднем и 23:59. Используйте% t, если это единственный символ в пользовательском числовом формате.

• % tt — для регионов, в которых используются 12-часовые часы, отображается заглавная буква AM с любым часом до полудня; отображает верхний регистр PM с любым часом между полуднем и 23:59. Для локалей, в которых используются 24-часовые часы, ничего не отображается.

• % y — отображает номер года (0-9) без начальных нулей. Используйте % y, если это единственный символ в пользовательском числовом формате.

• % yy — отображает год в двузначном числовом формате с начальным нулем, если это применимо.

• % yyy — отображает год в четырехзначном числовом формате.

• % yyyy — отображает год в четырехзначном числовом формате.

• % z — отображает смещение часового пояса без начального нуля (например, -8). Используйте % z, если это единственный символ в вашем пользовательском числовом формате.

• % zz — отображает. смещение часового пояса с ведущим нулем (например, -08)

• % zzz — отображает полное смещение часового пояса (например, -08: 00).

% d — отображает день в виде числа без начального нуля (например, 5).

% dd — отображает день в виде числа с начальным нулем (например, 05).

% ddd — отображает день в виде сокращения (например, вс).

% dddd — отображает день как полное имя (например, воскресенье).

% M — отображает месяц в виде числа без начального нуля (например, январь представлен как 1).

% MM — отображает месяц в виде числа с начальным нулем (например, январь представлен как 01).

% MMM — отображает месяц как сокращение (например, январь представлен как январь).

% MMMM — отображает месяц как полное название месяца (например, январь).

% gg — отображает строку периода / эры (например, AD).

% h — Отображение часа в виде числа без начальных нулей с использованием 12-часовых часов (например, 1:15:15 PM). Используйте % h, если это единственный символ в вашем пользовательском числовом формате.

% чч — отображает час в виде числа с ведущими нулями, используя 12-часовые часы (например, 01:15:15 PM).

% H — Отображение часа в виде числа без начальных нулей с использованием 24-часовых часов (например, 13:15:15, 1:15:15). Используйте% H, если это единственный символ в вашем пользовательском числовом формате.

% ЧЧ — отображает часы в виде числа с ведущими нулями, используя 24-часовые часы (например, 13:15:15, 1:15:15).

% m — отображает минуты в виде числа без начальных нулей (например, 2: 1: 15). Используйте% m, если это единственный символ в вашем пользовательском числовом формате.

% mm — отображает минуты в виде числа с ведущими нулями (например, 2:01:15).

% s — отображает секунду как число без начальных нулей (например, 2: 15: 5). Используйте% s, если это единственный символ в вашем пользовательском числовом формате.

% ss — отображает секунду как число с ведущими нулями (например, 2:15:05).

% f — отображает доли секунд. Например, ff отображает сотые доли секунды, тогда как ffff отображает десятитысячные доли секунды. Вы можете использовать до семи символов f в своем пользовательском формате. Используйте % f, если это единственный символ в вашем пользовательском числовом формате.

% t — использует 12-часовые часы и отображает заглавную букву A в течение любого часа до полудня; отображает заглавную букву P в течение любого часа между полуднем и 23:59. Используйте% t, если это единственный символ в пользовательском числовом формате.

% tt — для регионов, в которых используются 12-часовые часы, отображается заглавная буква AM с любым часом до полудня; отображает верхний регистр PM с любым часом между полуднем и 23:59. Для локалей, в которых используются 24-часовые часы, ничего не отображается.

% y — отображает номер года (0-9) без начальных нулей. Используйте % y, если это единственный символ в пользовательском числовом формате.

% yy — отображает год в двузначном числовом формате с начальным нулем, если это применимо.

% yyy — отображает год в четырехзначном числовом формате.

% yyyy — отображает год в четырехзначном числовом формате.

% z — отображает смещение часового пояса без начального нуля (например, -8). Используйте % z, если это единственный символ в вашем пользовательском числовом формате.

% zz — отображает. смещение часового пояса с ведущим нулем (например, -08)

% zzz — отображает полное смещение часового пояса (например, -08: 00).

Как вы можете заметить, строки форматирования чувствительны к регистру. Различное форматирование может быть получено с использованием другого регистра.

Изменение типов данных выходов формулы DAX

В формулах DAX тип данных для вывода определяется столбцами источника, и вы не можете явно указать тип данных результата. Это потому, что оптимальный тип данных определяется Power Pivot. Однако вы можете использовать неявные преобразования типов данных, выполняемые Power Pivot, для управления типом выходных данных. В противном случае вы можете использовать определенные функции DAX для преобразования типа выходных данных.

Использование неявных преобразований типов данных

• Чтобы преобразовать дату или числовую строку в число, умножьте на 1,0. Например, = (СЕГОДНЯ () + 5) * 1.0. Эта формула вычисляет текущую дату плюс 5 дней и преобразует результат в целочисленное значение.

  • Чтобы преобразовать дату, число или значение валюты в строку, объедините значение с пустой строкой. Например, = Сегодня () & «»

Чтобы преобразовать дату или числовую строку в число, умножьте на 1,0. Например, = (СЕГОДНЯ () + 5) * 1.0. Эта формула вычисляет текущую дату плюс 5 дней и преобразует результат в целочисленное значение.

Чтобы преобразовать дату, число или значение валюты в строку, объедините значение с пустой строкой. Например, = Сегодня () & «»

Использование функций DAX для преобразования типов данных

Вы можете использовать функции DAX для следующих целей:

• Преобразование действительных чисел в целые числа.
• Преобразование действительных чисел, целых чисел или дат в строки.
• Преобразование строк в реальные числа или даты.

Вы узнаете это в следующих разделах.

Преобразование вещественных чисел в целые

Вы можете использовать следующие функции DAX для преобразования действительных чисел в целые числа —

ROUND (<число>, <num_digits>) — округляет число до указанного числа цифр и возвращает десятичное число.

CEILING (<число>, <значимость>) — округляет число до ближайшего целого или кратного значению и возвращает десятичное число.

FLOOR (<число>, <значимость>) — округляет число вниз до нуля, до ближайшего кратного значения и возвращает десятичное число.

Преобразование действительных чисел, целых чисел или дат в строки

Вы можете использовать следующие функции DAX для преобразования действительных чисел, целых чисел или дат в строки:

ИСПРАВЛЕНО (<number>, [<decimals>], [<no_comma>]) — округляет число и возвращает результат в виде текста. Количество цифр справа от десятичной точки равно 2 или указанному количеству десятичных знаков. Результат с запятыми или опционально без запятых.

FORMAT (<value>, <format_string>) — преобразует значение в текст в соответствии с указанным форматом.

Вы уже узнали об использовании функции Format для преобразования дат в строки.

Преобразование строк в реальные числа или даты

Вы можете использовать следующие функции DAX для преобразования строк в реальные числа или даты:

VALUE (<текст>) — преобразует текстовую строку, представляющую число, в число.

DATEVALUE (date_text) — преобразует дату в виде текста в дату в формате datetime.

TIMEVALUE (time_text) — преобразует время в текстовом формате во время в формате datetime.