Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
- DAX функция FORMAT в Power BI и Power Pivot
- Шаблоны формата чисел
- Шаблоны формата дат
- Шаблоны формата времени
- Совместное использование шаблонов
- DAX функция FORMAT и первый параметр
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы разберем одну из часто используемых в Power BI (Power Pivot) и популярных текстовых функций языка DAX — функцию FORMAT (формат).
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
DAX функция FORMAT в Power BI и Power Pivot
DAX функция FORMAT () — меняет различные типы данных (числа, дата, время) на текстовый тип данных с возможностью задать нужный формат вывода информации.
Синтаксис: FORMAT (Значение; Формат), где:
- Значение — значение с типом данных число, дата, время
- Формат — шаблон формата
Шаблоны формата чисел
Ниже приведены основные и часто используемые шаблоны формата чисел, а также, примеры формул на их основе:
- «General Number» — выводит число
Пример: FORMAT (12345,678; "General Number") Результат: 12345,678
- «Currency» — денежный формат. Выводит число, разделяя его на группы разрядов на основании параметров локали ПК
Пример: FORMAT (12345,678; "Currency") Результат: 12 345,68 Руб
- «Fixed» — округляет число до 2 знаков после запятой
Пример: FORMAT (12345,67867; "Fixed") Результат: 12345,68
- «Standard» — выводит число, разделенное на группы разрядов, округляя его до 2 знаков после запятой
Пример: FORMAT (12345,67867; "Standard") Результат: 12 345,68
- «Percent» — умножает число на 100, преобразуя его в проценты (%). После запятой число округляется до 2 знаков
Пример: FORMAT (0,67867; "Percent") Результат: 67,87%
- «Scientific» — преобразует число в научный вид
Пример: FORMAT (1234567; "Scientific") Результат: 1,23E+06
- «Yes/No» — выводит «Да», если число не равно 0 и «Нет», если равно 0
Пример: а) FORMAT (1234567; "Yes/No"); б) FORMAT (0; "Yes/No") Результат а: да Результат б: нет
- «True/False» — выводит «TRUE» (Истина), если число не равно 0 и «FALSE» (Ложь), если равно 0
Пример: а) FORMAT (1234567; "True/False"); б) FORMAT (0; "True/False") Результат а: Истина Результат б: Ложь
- «On/Off» — выводит «Вкл», если число не равно 0 и «Выкл», если равно 0
Пример: а) FORMAT (1234567; "On/Off"); б) FORMAT (0; "On/Off") Результат а: Вкл Результат б: Выкл
Шаблоны формата дат
Ниже приведены основные и часто используемые шаблоны формата дат, а также, примеры формул на их основе:
- «D» — выводит номер дня месяца (без нулей)
Пример: а) FORMAT (DATE (2018;10;02); "D"); б) FORMAT (DATE (2018;10;23); "D") Результат а: 2 Результат б: 23
- «DD» — выводит номер дня месяца (с нулём)
Пример: а) FORMAT (DATE (2018;10;02); "DD"); б) FORMAT (DATE (2018;10;23); "DD") Результат а: 02 Результат б: 23
- «DDD» — преобразует исходную дату в день недели (сокращенное название — Пт, Сб, Вс)
Пример: FORMAT(DATE(2018;10;02); "DDD") Результат: Вт
- «DDDD» — преобразует исходную дату в день недели (полное название)
Пример: FORMAT(DATE(2018;10;02); "DDDD") Результат: вторник
- «M» — выводит номер месяца в году (без нулей)
Пример: а) FORMAT (DATE (2018;01;23); "M"); б) FORMAT (DATE (2018;11;23); "M") Результат а: 1 Результат б: 11
- «MM» — выводит номер месяца в году (c нулём)
Пример: а) FORMAT (DATE (2018;01;23); "MM"); б) FORMAT (DATE (2018;11;23); "MM") Результат а: 01 Результат б: 11
- «MMM» — преобразует исходную дату в месяц (сокращенное название — дек)
Пример: FORMAT (DATE (2018;11;23); "MMM") Результат: ноя
- «MMMM» — преобразует исходную дату в месяц (полное название)
Пример: FORMAT (DATE (2018;11;23); "MMMM") Результат: Ноябрь
- «yy» — выводит год в формате 2 цифр
Пример: а) FORMAT (DATE (2018;11;23); "yy"); б) FORMAT (DATE (2001;11;23); "yy") Результат а: 18 Результат б: 01
- «yyyy» — выводит год в формате 4 цифр
Пример: FORMAT (DATE (2018;11;23); "yyyy") Результат: 2018
Шаблоны формата времени
Ниже приведены основные и часто используемые шаблоны формата времени, а также, примеры формул на их основе:
- «H» — преобразует исходное время в часы (без нулей)
Пример: FORMAT (TIME (05;50;16); "H") Результат: 5
- «HH» — преобразует исходное время в часы (с нулём)
Пример: FORMAT (TIME (05;50;16); "HH") Результат: 05
- «m» — преобразует исходное время в минуты (без нулей)
Пример: FORMAT (TIME (10;07;16); "HH:m") Результат: 10:7
- «mm» — преобразует исходное время в минуты (с нулём)
Пример: FORMAT (TIME (10;07;16); "HH:mm") Результат: 10:07
- «s» — преобразует исходное время в секунды (без нулей)
Пример: FORMAT (TIME (10;18;03); "s") Результат: 3
- «ss» — преобразует исходное время в секунды (с нулём)
Пример: FORMAT (TIME (10;18;03); "ss") Результат: 03
Совместное использование шаблонов
Выше приведенные шаблоны функции FORMAT можно использовать как одиночно, так и совмещая их так, как это будет удобно Вам в конкретном случае.
Например, совместив сразу несколько шаблонов дат друг с другом и разделив их нужными нам разделителями, мы получим удобный для нас текстовый вывод даты в Power BI:
Объединенный шаблон форматов дат: "DDDD MMMM'yy"
И, как результат, мы получим следующий вывод даты:
Или мы можем объединить сразу несколько шаблонов времени друг с другом, также, разделив их нужным разделителем:
Объединенный шаблон форматов времени: FORMAT (TIME (21;50;16); "HH:mm:ss")
И, как результат, в Power BI мы получим следующий вывод времени:
DAX функция FORMAT и первый параметр
Во всех выше приведенных примерах мы в первый параметр вставляли либо просто значение, либо другую DAX функцию, возвращающую также значение.
Но, в реальной работе в DAX формулах чаще всего нужно будет указывать там ссылку на столбец, содержащий какие-то значения. Поэтому давайте рассмотрим еще один пример использования функции FORMAT, только теперь в качестве первого параметра будем указывать столбец.
Итак, в Power BI у меня имеется исходная таблица со столбцом дат:
Создадим в Power BI Desktop во вкладке «Моделирование» два вычисляемых столбца в этой таблице по следующим формулам с использованием рассматриваемой нами функции:
Формула 1: Год = FORMAT ('Таблица'[Даты]; "yyyy") Формула 2: Месяц = FORMAT ('Таблица'[Даты]; "mmmm")
Как итог, мы получили таблицу, где вместо одного столбца с простыми датами появились еще 2 дополнительных столбца со значениями годов и месяцев.
И, теперь по этим 2 столбцам мы уже можем свободно создавать в DAX формулы с фильтрами по годам или месяцам. Например, при помощи функции FILTER (подробно ознакомиться с функцией FILTER Вы можете в данной статье):
Год2018 = FILTER( 'Таблица'; 'Таблица'[Год] = "2018" )
И FILTER нам возвращает таблицу, отфильтрованную по значению 2018 в созданном нами вычисляемом столбце на основе функции FORMAT:
На этом, с разбором функции FORMAT в Power BI и Power Pivot, в данной статье все.
Пожалуйста, оцените статью:
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
А можно как нибудь влиять на язык, на котором выводим написание месяца из даты?
=FORMAT([Date of creation]; «[$-en]MMMM») — типо как в Excel… Правда в здесь этот номер не прокатывает
Да, есть определенные тонкости в функции FORMAT, которые позволяют настроить вывод более точно. Я чуть позже напишу дополнительную статью по этим дополнительным параметрам.
В Вашем случае, какой именно формат вывода Вы хотели бы получить?
у функции FORMAT если 3-й аргумент (необязательный) в нем надо прописать локаль «ru-RU» (для отображения языка в русском варианте ну или какой вам нужно)
А как в функции Format использовать квартал?
В функции FORMAT, к сожалению, нет формата квартала, по крайней мере я этого не знаю и нигде никогда не встречал таковых параметров.
Можно через функцию FORMAT вывести номер месяца, и затем все это обернуть в функцию SWITCH, где напротив каждого месяца уже прописать наименование квартала в том формате, в котором нужно
=CONCATENATE(«Qtr», INT((MONTH([Date of creation]) + 2) / 3))
Спасибо, Тарас, интересное решение!