Excel DAX - Функции

Большинство из DAX functionsимеют те же имена и функции, что и функции Excel. Однако функции DAX были изменены для использования типов данных DAX и для работы с таблицами и столбцами.

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

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

Что такое функция DAX?

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

Функции Excel и функции DAX

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

Сходства между функциями Excel и функциями DAX

  • Многие функции DAX имеют то же имя и такое же общее поведение, что и функции Excel.

  • В DAX есть функции поиска, которые аналогичны функциям поиска по массивам и векторам в Excel.

Различия между функциями Excel и функциями DAX

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

  • Вы не можете использовать функции DAX в формуле Excel или использовать формулы / функции Excel в DAX без необходимых изменений.

  • Функции Excel используют ссылку на ячейку или диапазон ячеек в качестве ссылки. Функции DAX никогда не принимают ссылку на ячейку или диапазон ячеек в качестве ссылки, а вместо этого принимают в качестве ссылки столбец или таблицу.

  • Функции даты и времени Excel возвращают целое число, представляющее дату в виде серийного номера. Функции даты и времени DAX возвращают тип данных datetime, который есть в DAX, но не в Excel.

  • В Excel нет функций, возвращающих таблицу, но некоторые функции могут работать с массивами. Многие функции DAX могут легко ссылаться на полные таблицы и столбцы для выполнения вычислений и возврата таблицы или столбца значений. Эта способность DAX расширяет возможности Power Pivot, Power View и Power BI, где используется DAX.

  • Функции поиска DAX требуют, чтобы между таблицами была установлена ​​связь.

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

Чтобы узнать о типах данных DAX и преобразовании типов данных, обратитесь к главе - Справочник по синтаксису DAX.

Типы функций DAX

DAX поддерживает следующие типы функций.

  • Табличные функции DAX
    • Функции фильтра DAX
    • Функции агрегирования DAX
    • Функции DAX Time Intelligence
  • Функции даты и времени DAX
  • Информационные функции DAX
  • Логические функции DAX
  • Математические и триггерные функции DAX
  • Другие функции DAX
  • Родительские и дочерние функции DAX
  • Статистические функции DAX
  • Текстовые функции DAX

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

Функции аналитики времени DAX и функции фильтров DAX являются мощными и требуют особого упоминания. Дополнительные сведения см. В главах «Общие сведения о функциях DAX Time Intelligence и DAX Filter».

Табличные функции DAX

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

  • Функции агрегирования DAX
  • Функции фильтра DAX
  • Функции DAX Time Intelligence

Понимание функций DAX с табличными значениями поможет вам эффективно писать формулы DAX.

Функции агрегирования DAX

Функции агрегирования DAX агрегируют любое выражение по строкам таблицы и полезны в вычислениях.

Ниже приведены некоторые функции агрегирования DAX.

  • ADDCOLUMNS (<таблица>, <имя>, <выражение>, [<имя>, <выражение>]…)

  • СРЕДНИЙ (<столбец>)

  • СРЕДНЕЕ (<столбец>)

  • AVERAGEX (<таблица>, <выражение>)

  • COUNT (<столбец>)

  • COUNTA (<столбец>)

  • СЧЕТЧИК (<таблица>, <выражение>)

  • СЧИТАТЬПУСТОТЫ (<столбец>)

  • СЧЕТЧИК (<таблица>)

  • COUNTX (<таблица>, <выражение>)

  • CROSSJOIN (<table1>, <table2>, [<table3>]…)

  • DISTINCTCOUNT (<столбец>)

  • СОЗДАТЬ (<таблица1>, <таблица2>)

  • ОБЩИЙ ВСЕ (<таблица1>, <таблица 2>)

  • МАКС (<столбец>)

  • МАКСА (<столбец>)

  • MAXX (<таблица>, <выражение>)

  • МИН (<столбец>)

  • MINA (<столбец>)

  • MINX (<таблица>, <выражение>)

  • ПРОДУКТ (<столбец>)

  • PRODUCTX (<таблица>, <выражение>)

  • СТРОКА (<имя>, <выражение>, [<имя>, <выражение>]…)

  • SELECTCOLUMNS (<таблица>, <имя>, <скалярное_выражение>,

  • [<name>, <scalar_expression>]…)

  • СУММ (<столбец>)

  • SUMMARIZE (<таблица>, <groupBy_columnName>, [<groupBy_columnName>]…, [<name>, <expression>]…)

  • СУММХ (<таблица>, <выражение>)

  • TOPN (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

Функции фильтра DAX

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

Ниже приведены некоторые функции фильтра DAX:

  • ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>]…, <table>, <groupingColumn>, [<groupingColumn>]… [filterTable]…)

  • ВСЕ ({<таблица> | <столбец>, [<столбец>], [<столбец>]…})

  • ALLEXCEPT (<таблица>, <столбец>, [<столбец>]…)

  • ALLNOBLANKROW(<table>|<column>)

  • ВЫБРАННЫЕ ВСЕ ([<имя таблицы> | <имя столбца>])

  • РАССЧИТАТЬ (<выражение>, <фильтр1>, <фильтр2>…)

  • ВЫЧИСЛИВАЕМЫЙ (<выражение>, <фильтр1>, <фильтр2>…)

  • КРОССФИЛЬТР (<имя столбца1>, <имя столбца2>, <направление>)

  • DISTINCT (<столбец>)

  • РАНЬШЕ (<столбец>, <число>)

  • EARLIEST(<column>)

  • FILTER(<table>,<filter>)

  • FILTERS(<columnName>)

  • HASONEFILTER(<columnName>)

  • HASONEVALUE(<columnName>)

  • ISCROSSFILTERED (<имя столбца>)

  • ИСЧИТАНО (<имя столбца>)

  • KEEPFILTERS (<выражение>)

  • RELATED(<column>)

  • RELATEDTABLE(<tableName>)

  • SUBSTITUTEWITHINDEX (<таблица>, <indexColumnName>, <indexColumnsTable>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…])

  • USERELATIONSHIP(<columnName1>,<columnName2>)

  • VALUES(<TableNameOrColumnName>)

Функции DAX Time Intelligence

Функции DAX Time Intelligence возвращают таблицу дат или используют таблицу дат для вычисления агрегирования. Эти функции DAX помогают создавать вычисления, которые поддерживают потребности анализа бизнес-аналитики, позволяя манипулировать данными, используя периоды времени, включая дни, месяцы, кварталы и годы.

Ниже приведены некоторые функции DAX Time Intelligence.

  • CLOSINGBALANCEMONTH (<выражение>, <даты> [, <фильтр>])

  • CLOSINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>])

  • CLOSINGBALANCEYEAR (<выражение>, <даты>, [<filter>], [<year_end_date>])

  • DATEADD (<даты>, <число_интервалов>, <интервал>)

  • ДАТЫ МЕЖДУ (<даты>, <начальная_ дата>, <конечная_ дата>)

  • DATESINPERIOD (<даты>, <начальная_дата>, <количество_интервалов>, <интервал>)

  • DATESMTD (<даты>)

  • DATESQTD (<даты>)

  • DATESYTD (<даты>, [<year_end_date>])

  • ENDOFMONTH (<даты>)

  • ENDOFQUARTER (<даты>)

  • ENDOFYEAR (<даты>, [<дата_конца_ года>])

  • FIRSTDATE (<даты>)

  • FIRSTNONBLANK (<столбец>, <выражение>)

  • LASTDATE (<даты>)

  • LASTNONBLANK (<столбец>, <выражение>)

  • НА СЛЕДУЮЩИЙ ДЕНЬ (<даты>)

  • NEXTMONTH (<даты>)

  • СЛЕДУЮЩАЯ ЧАСТЬ (<даты>)

  • NEXTYEAR (<даты>, [<year_end_date>])

  • OPENINGBALANCEMONTH (<выражение>, <даты>, [<фильтр>])

  • OPENINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>])

  • OPENINGBALANCEYEAR (<выражение>, <даты>, [<filter>], [<year_end_date>])

  • ПАРАЛЛЕЛЬНЫЙ ПЕРИОД (<даты>, <число_интервалов>, <интервал>)

  • PREVIOUSDAY(<dates>)

  • PREVIOUSMONTH(<dates>)

  • PREVIOUSQUARTER(<dates>)

  • ПРЕДЫДУЩИЙ ГОД (<даты>, [<дата_конечного_года>])

  • SAMEPERIODLASTYEAR(<dates>)

  • STARTOFMONTH(<dates>)

  • STARTOFQUARTER(<dates>)

  • STARTOFYEAR(<dates>)

  • TOTALMTD (<выражение>, <даты>, [<фильтр>])

  • TOTALQTD (<выражение>, <даты>, [<фильтр>])

  • TOTALYTD (<выражение>, <даты>, [<фильтр>], [<дата_конца года>])

Функции даты и времени DAX

Функции даты и времени DAX аналогичны функциям даты и времени Excel. Однако функции DAX Date и Time основаны на типе данных DAX datetime.

Ниже приведены функции даты и времени DAX.

  • ДАТА (<год>, <месяц>, <день>)
  • DATEVALUE(date_text)
  • DAY(<date>)
  • ДАТА (<начальная_дата>, <месяцы>)
  • EOMONTH (<start_date>, <months>)
  • HOUR(<datetime>)
  • MINUTE(<datetime>)
  • MONTH(<datetime>)
  • NOW()
  • SECOND(<time>)
  • ВРЕМЯ (час, минута, секунда)
  • TIMEVALUE(time_text)
  • TODAY()
  • WEEKDAY (<дата>, <return_type>)
  • WEEKNUM (<дата>, <возвращаемый_тип>)
  • YEAR(<date>)
  • ГОД (<дата_пуска>, <дата_кончания>, <основа>)

Информационные функции DAX

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

Ниже приведены некоторые информационные функции DAX.

  • СОДЕРЖИТ (<таблица>, <имя столбца>, <значение>, [<имя столбца>, <значение>]…)

  • CUSTOMDATA()

  • ISBLANK(<value>)

  • ISERROR(<value>)

  • ISEVEN(number)

  • ISLOGICAL(<value>)

  • ISNONTEXT(<value>)

  • ISNUMBER(<value>)

  • ISONORAFTER (<scalar_expression>, <scalar_expression>, [sort_order], [<scalar_expression>, <scalar_expression>, [sort_order]]…)

  • ISTEXT(<value>)

  • LOOKUPVALUE (<result_columnName>, <search_columnName>, <search_value>, [<search_columnName>, <search_value>]…)

  • USERNAME()

Логические функции DAX

Логические функции DAX возвращают информацию о значениях в выражении. Например, функция DAX TRUE позволяет узнать, возвращает ли вычисляемое выражение значение TRUE.

Ниже приведены логические функции DAX -

  • AND(<logical1>,<logical2>)
  • FALSE()
  • ЕСЛИ (логический_тест>, <значение_если_ истинно>, значение_если_ ложь)
  • ЕСЛИОШИБКА (значение; значение_если_ошибка)
  • NOT(<logical>)
  • OR(<logical1>,<logical2>)
  • SWITCH (<выражение>, <значение>, <результат>, [<значение>, <результат>]…, [<else>])
  • TRUE()

Математические и триггерные функции DAX

Математические и тригонометрические функции DAX очень похожи на математические и тригонометрические функции Excel.

Ниже приведены некоторые функции DAX Math и Trig.

  • ABS(<number>)
  • ACOS(number)
  • ACOSH(number)
  • ASIN(number)
  • ASINH(number)
  • ATAN(number)
  • ATANH(number)
  • ПОТОЛОК (<число>, <значение>)
  • COMBIN (число; выбранное число)
  • КОМБИНА (число; выбранное число)
  • COS(number)
  • COSH(number)
  • CURRENCY(<value>)
  • DEGREES(angle)
  • РАЗДЕЛЕНИЕ (<числитель>, <знаменатель>, [<альтернативный результат>])
  • EVEN(number)
  • EXP(<number>)
  • FACT(<number>)
  • ЭТАЖ (<число>, <значение>)
  • НОД (число1; [число2]; ...)
  • INT(<number>)
  • ISO.CEILING (<число>, [<значение>])
  • НОК (число1; [число2]; ...)
  • LN(<number>)
  • LOG(<number>,<base>)
  • LOG10(<number>)
  • INT(<number>)
  • ОКРУГЛ (<число>; <несколько>)
  • ODD(number)
  • PI()
  • МОЩНОСТЬ (<число>, <мощность>)
  • PRODUCT(<column>)
  • PRODUCTX (<таблица>, <выражение>)
  • ЧИСЛО (<числитель>, <знаменатель>)
  • RADIANS(angle)
  • RAND()
  • RANDBETWEEN(<bottom>,<top>)
  • ОКРУГЛ (<число>, <число_цифров>)
  • ОКРУГЛ ВНИЗ (<число>, <число_цифров>)
  • ОКРУГЛ ВВЕРХ (<число>, <число_цифров>)
  • SIN(number)
  • SINH(number)
  • SIGN(<number>)
  • SQRT(<number>)
  • SUM(<column>)
  • СУММХ (<таблица>, <выражение>)
  • TAN(number)
  • TANH(number)
  • TRUNC(<number>,<num_digits>)

Другие функции DAX

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

Ниже приведены некоторые другие функции DAX -

  • ИСКЛЮЧЕНИЕ (<выражение_таблицы1>, <выражение_таблицы2>

  • GROUPBY (<таблица>, [<groupBy_columnName1>], [<name>, <expression>]…)

  • ПЕРЕСЕЧЕНИЕ (<выражение_таблицы1>, <выражение_таблицы2>)

  • ISEMPTY(<table_expression>)

  • NATURALINNERJOIN (<leftJoinTable>, <rightJoinTable>)

  • NATURALLEFTOUTERJOIN (<leftJoinTable>, <rightJoinTable>)

  • SUMMARIZECOLUMNS (<groupBy_columnName>, [<groupBy_columnName>]…, [<filterTable>]…, [<name>, <expression>]…)

  • UNION (<таблица_выражение1>, <выражение_таблицы2>, [<выражение_таблицы>]…)

  • VAR <имя> = <выражение>

Родительские и дочерние функции DAX

Функции DAX Parent и Child полезны при управлении данными, которые представлены как иерархия родитель / потомок в модели данных.

Ниже приведены некоторые родительские и дочерние функции DAX.

  • ПУТЬ (<ID_columnName>, <parent_columnName>)
  • PATHCONTAINS (<путь>, <элемент>)
  • ПУТЬ (<путь>, <позиция>, [<тип>])
  • PATHITEMREVERSE (<путь>, <позиция>, [<тип>])
  • PATHLENGTH(<path>)

Статистические функции DAX

Статистические функции DAX очень похожи на статистические функции Excel.

Ниже приведены некоторые статистические функции DAX.

  • БЕТАРАСП (x; альфа; бета; накопительное; [A]; [B])

  • БЕТА.ОБР (вероятность; альфа; бета; [A], [B])

  • CHISQ.INV (вероятность; степень_свободы)

  • CHISQ.INV.RT (вероятность; степень_свободы)

  • КОНФИДЕНЦИЯ.НОРМ (альфа; стандартное_откл; размер)

  • CONFIDENCE.T (альфа; стандартное_откл; размер)

  • DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2 ..., {{Value1, Value2 ...}, {ValueN, ValueN + 1 ...} ...})

  • ЭКСПОН.РАСП (x; лямбда; накопительное)

  • GEOMEAN(<column>)

  • GEOMEANX (<таблица>, <выражение>)

  • MEDIAN(<column>)

  • MEDIANX (<таблица>, <выражение>)

  • PERCENTILE.EXC (<столбец>, <k>)

  • PERCENTILE.INC (<столбец>, <k>)

  • PERCENTILEX.EXC (<таблица>, <выражение>, k)

  • PERCENTILEX.EXC (<таблица>, <выражение>, k)

  • ПУАССОН.РАСП (x; среднее; накопительное)

  • RANK.EQ (<значение>, <имя столбца> [, <порядок>])

  • RANKX (<таблица>, <выражение> [, <значение> [, <порядок> [, <ties>]]])

  • SAMPLE (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

  • STDEV.P(<ColumnName>)

  • STDEV.S(<ColumnName>)

  • STDEVX.P (<таблица>, <выражение>)

  • STDEVX.S (<таблица>, <выражение>)

  • SQRTPI(number)

  • VAR.P(<columnName>)

  • VAR.S(<columnName>)

  • VARX.P (<таблица>, <выражение>)

  • VARX.S (<таблица>, <выражение>)

  • XIRR (<таблица>, <значения>, <даты>, [предположить])

  • XNPV (<таблица>, <значения>, <даты>, <скорость>)

Текстовые функции DAX

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

Ниже приведены некоторые функции DAX Text.

  • BLANK()
  • CODE(text)
  • СЦЕПИТЬ (<текст1>, <текст2>)
  • КОНКАТЕНАТЕКС (<таблица>, <выражение>, [разделитель])
  • EXACT(<text1>,<text2>)
  • НАЙТИ (<find_text>, <within_text>, [<start_num>], [<NotFoundValue>])
  • ИСПРАВЛЕНО (<число>, <десятичные числа>, <количество_запятых>)
  • ФОРМАТ (<значение>, <формат_строки>)
  • ВЛЕВО (<текст>, <количество_символов>)
  • LEN(<text>)
  • LOWER(<text>)
  • MID (<текст>, <начальное_число>, <число_символов>)
  • ЗАМЕНИТЬ (<старый_текст>, <начальное_число>, <число_ символов>, <новый_текст>)
  • ПОВТОР (<текст>, <количество_раз>)
  • ВПРАВО (<текст>, <число_ символов>)
  • ПОИСК (<find_text>, <within_text>, [<start_num>], [<NotFoundValue>])
  • ПОДСТАВИТЬ (<текст>, <старый_текст>, <новый_текст>, <экземпляр_номер>)
  • TRIM(<text>)
  • ВЕРХНИЙ (<текст>)
  • VALUE(<text>)