Excel DAX - Fonctions

Vous trouverez ci-dessous la liste des fonctions d'agrégation DAX.

ADDCOLUMNS (<table>, <nom>, <expression>, [<nom>, <expression>]…)
MOYENNE (<colonne>)
MOYENNEA (<colonne>)
AVERAGEX (<table>, <expression>)
COUNT (<colonne>)
COUNTA (<colonne>)
COUNTAX (<table>, <expression>)
COUNTBLANK (<colonne>)
COUNTROWS (<table>)
COUNTX (<table>, <expression>)
CROSSJOIN (<table1>, <table2>, [<table3>]…)
DISTINCTCOUNT (<colonne>)
GÉNÉRER (<table1>, <table2>)
GÉNÉRER TOUT (<table1>, <table2>)
MAX (<colonne>)
MAXA (<colonne>)
MAXX (<table>, <expression>)
MIN (<colonne>)
MINA (<colonne>)
MINX (<table>, <expression>)
PRODUIT (<colonne>)
PRODUCTX (<table>, <expression>)
ROW (<nom>, <expression>, [<nom>, <expression>]…)
SELECTCOLUMNS (<table>, <nom>, <expression_calaire>,
[<nom>, <expression_scalaire>]…)
SOMME (<colonne>)
SUMMARIZE (<table>, <groupBy_columnName>, [<groupBy_columnName>]…, [<name>, <expression>]…)
SUMX (<table>, <expression>)
TOPN (<n_valeur>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

Vous trouverez ci-dessous la liste des fonctions de filtre DAX.

ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>]…, <table>, <groupingColumn>, [<groupingColumn>]… [filterTable]…)
TOUT ({<table> | <colonne>, [<colonne>], [<colonne>]…})
ALLEXCEPT (<table>, <colonne>, [<colonne>]…)
ALLNOBLANKROW(<table>|<column>)
ALLSELECTED ([<tableName> | <columnName>])
CALCULER (<expression>, <filter1>, <filter2>…)
CALCULATETABLE (<expression>, <filter1>, <filter2>…)
CROSSFILTER (<columnName1>, <columnName2>, <direction>)
DISTINCT (<colonne>)
EARLIER (<colonne>, <numéro>)
EARLIEST(<column>)
FILTER(<table>,<filter>)
FILTERS(<columnName>)
HASONEFILTER(<columnName>)
HASONEVALUE(<columnName>)
ISCROSSFILTERED (<nomcolonne>)
ISFILTERED (<nomcolonne>)
KEEPFILTERS (<expression>)
RELATED(<column>)
RELATEDTABLE(<tableName>)
SUBSTITUTEWITHINDEX (<table>, <indexColumnName>, <indexColumnsTable>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…])
USERELATIONSHIP(<columnName1>,<columnName2>)
VALUES(<TableNameOrColumnName>)

Vous trouverez ci-dessous la liste des fonctions DAX Time Intelligence.

CLOSINGBALANCEMONTH (<expression>, <dates> [, <filter>])
CLOSINGBALANCEQUARTER (<expression>, <dates>, [<filter>])
CLOSINGBALANCEYEAR (<expression>, <dates>, [<filter>], [<year_end_date>])
DATEADD (<dates>, <number_of_intervals>, <interval>)
DATESBETWEEN (<dates>, <start_date>, <end_date>)
DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>)
DATESMTD (<dates>)
DATESQTD (<dates>)
DATESYTD (<dates>, [<year_end_date>])
ENDOFMONTH (<dates>)
FIN DU QUARTIER (<dates>)
ENDOFYEAR (<dates>, [<year_end_date>])
FIRSTDATE (<dates>)
FIRSTNONBLANK (<colonne>, <expression>)
LASTDATE (<dates>)
LASTNONBLANK (<colonne>, <expression>)
NEXTDAY (<dates>)
NEXTMONTH (<dates>)
QUARTIER SUIVANT (<dates>)
NEXTYEAR (<dates>, [<year_end_date>])
OPENINGBALANCEMONTH (<expression>, <dates>, [<filter>])
OPENINGBALANCEQUARTER (<expression>, <dates>, [<filter>])
OPENINGBALANCEYEAR (<expression>, <dates>, [<filter>], [<year_end_date>])
PARALLELPERIOD (<dates>, <number_of_intervals>, <interval>)
PREVIOUSDAY(<dates>)
PREVIOUSMONTH(<dates>)
PREVIOUSQUARTER(<dates>)
PREVIOUSYEAR (<dates>, [<year_end_date>])
SAMEPERIODLASTYEAR(<dates>)
STARTOFMONTH(<dates>)
STARTOFQUARTER(<dates>)
STARTOFYEAR(<dates>)
TOTALMTD (<expression>, <dates>, [<filtre>])
TOTALQTD (<expression>, <dates>, [<filtre>])
TOTALYTD (<expression>, <dates>, [<filter>], [<year_end_date>])

Vous trouverez ci-dessous la liste des fonctions de date et d'heure DAX.

DATE (<année>, <mois>, <jour>)
DATEVALUE(date_text)
DAY(<date>)
EDATE (<start_date>, <mois>)
EOMONTH (<date_début>, <mois>)
HOUR(<datetime>)
MINUTE(<datetime>)
MONTH(<datetime>)
NOW()
SECOND(<time>)
TIME (heure, minute, seconde)
TIMEVALUE(time_text)
TODAY()
WEEKDAY (<date>, <return_type>)
SEMAINE (<date>, <type_retour>)
YEAR(<date>)
YEARFRAC (<start_date>, <end_date>, <basis>)

Vous trouverez ci-dessous la liste des fonctions d'informations DAX.

CONTIENT (<table>, <columnName>, <value>, [<columnName>, <value>]…)
CUSTOMDATA()
ISBLANK(<value>)
ISERROR(<value>)
ISEVEN(number)
ISLOGICAL(<value>)
ISNONTEXT(<value>)
ISNUMBER(<value>)
ISONORAFTER (<expression_calaire>, <expression_calaire>, [ordre_tri], [<expression_calaire>, <expression_scalaire>, [ordre_ tri]]…)
ISTEXT(<value>)
LOOKUPVALUE (<result_columnName>, <search_columnName>, <search_value>, [<search_columnName>, <search_value>]…)
USERNAME()

Vous trouverez ci-dessous la liste des fonctions logiques DAX.

AND(<logical1>,<logical2>)
FALSE()
IF (test_logique>, <value_if_true>, value_if_false)
IFERROR (valeur, valeur_if_error)
NOT(<logical>)
OR(<logical1>,<logical2>)
SWITCH (<expression>, <valeur>, <résultat>, [<valeur>, <résultat>]…, [<else>])
TRUE()

Vous trouverez ci-dessous la liste des fonctions DAX Math et Trig.

ABS(<number>)
ACOS(number)
ACOSH(number)
ASIN(number)
ASINH(number)
ATAN(number)
ATANH(number)
PLAFOND (<numéro>, <importance>)
COMBIN (nombre, nombre_choisi)
COMBINA (nombre, nombre_choisi)
COS(number)
COSH(number)
CURRENCY(<value>)
DEGREES(angle)
DIVIDE (<numérateur>, <dénominateur>, [<alternateresult>])
EVEN(number)
EXP(<number>)
FACT(<number>)
PLANCHER (<numéro>, <importance>)
GCD (numéro1, [numéro2], ...)
INT(<number>)
PLAFOND ISO (<numéro>, [<importance>])
LCM (numéro1, [numéro2], ...)
LN(<number>)
LOG(<number>,<base>)
LOG10(<number>)
INT(<number>)
MROUND (<numéro>, <multiple>)
ODD(number)
PI()
PUISSANCE (<numéro>, <puissance>)
PRODUCT(<column>)
PRODUCTX (<table>, <expression>)
QUOTIENT (<numérateur>, <dénominateur>)
RADIANS(angle)
RAND()
RANDBETWEEN(<bottom>,<top>)
ROUND (<nombre>, <num_digits>)
ROUNDDOWN (<nombre>, <num_digits>)
ROUNDUP (<nombre>, <num_digits>)
SIN(number)
SINH(number)
SIGN(<number>)
SQRT(<number>)
SUM(<column>)
SUMX (<table>, <expression>)
TAN(number)
TANH(number)
TRUNC(<number>,<num_digits>)

Ci-dessous se trouve la liste des autres fonctions DAX.

SAUF (<expression_table1>, <expression_table2>
GROUPBY (<table>, [<groupBy_columnName1>], [<nom>, <expression>]…)
INTERSECT (<expression_table1>, <expression_table2>)
ISEMPTY(<table_expression>)
NATURALINNERJOIN (<leftJoinTable>, <rightJoinTable>)
NATURALLEFTOUTERJOIN (<leftJoinTable>, <rightJoinTable>)
SUMMARIZECOLUMNS (<groupBy_columnName>, [<groupBy_columnName>]…, [<filterTable>]…, [<name>, <expression>]…)
UNION (<table_expression1>, <table_expression2>, [<table_expression>]…)
VAR <nom> = <expression>

Vous trouverez ci-dessous la liste des fonctions DAX Parent et Child.

CHEMIN (<ID_columnName>, <parent_columnName>)
PATHCONTAINS (<chemin>, <item>)
PATHITEM (<chemin>, <position>, [<type>])
PATHITEMREVERSE (<chemin>, <position>, [<type>])
PATHLENGTH(<path>)

Ci-dessous se trouve la liste des fonctions statistiques DAX.

BETA.DIST (x, alpha, bêta, cumulatif, [A], [B])
BETA.INV (probabilité, alpha, bêta, [A], [B])
CHISQ.INV (probabilité, deg_liberté)
CHISQ.INV.RT (probabilité, deg_liberté)
CONFIDENCE.NORM (alpha, standard_dev, taille)
CONFIDENCE.T (alpha, standard_dev, taille)
DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2 ..., {{Value1, Value2 ...}, {ValueN, ValueN + 1 ...} ...})
EXPON.DIST (x, lambda, cumulatif)
GEOMEAN(<column>)
GEOMEANX (<table>, <expression>)
MEDIAN(<column>)
MEDIANX (<table>, <expression>)
PERCENTILE.EXC (<colonne>, <k>)
PERCENTILE.INC (<colonne>, <k>)
PERCENTILEX.EXC (<table>, <expression>, k)
PERCENTILEX.EXC (<table>, <expression>, k)
POISSON.DIST (x, moyenne, cumulée)
RANK.EQ (<valeur>, <columnName> [, <order>])
RANKX (<table>, <expression> [, <valeur> [, <order> [, <ties>]]])
EXEMPLE (<n_valeur>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)
STDEV.P(<ColumnName>)
STDEV.S(<ColumnName>)
STDEVX.P (<table>, <expression>)
STDEVX.S (<table>, <expression>)
SQRTPI(number)
VAR.P(<columnName>)
VAR.S(<columnName>)
VARX.P (<table>, <expression>)
VARX.S (<table>, <expression>)
XIRR (<table>, <values>, <dates>, [suppose])
XNPV (<table>, <valeurs>, <dates>, <rate>)

Vous trouverez ci-dessous la liste des fonctions DAX Text.

BLANK()
CODE(text)
CONCATENER (<texte1>, <texte2>)
CONCATENATEX (<table>, <expression>, [délimiteur])
EXACT(<text1>,<text2>)
FIND (<find_text>, <within_text>, [<start_num>], [<NotFoundValue>])
FIXE (<numéro>, <décimales>, <pas de virgule>)
FORMAT (<valeur>, <chaîne_format>)
GAUCHE (<text>, <num_chars>)
LEN(<text>)
LOWER(<text>)
MID (<texte>, <num_démarrage>, <num_chars>)
REMPLACER (<old_text>, <start_num>, <num_chars>, <new_text>)
REPT (<texte>, <num_heures>)
DROITE (<text>, <num_chars>)
RECHERCHE (<find_text>, <within_text>, [<start_num>], [<NotFoundValue>])
SUBSTITUT (<text>, <old_text>, <nouveau_texte>, <instance_num>)
TRIM(<text>)
UPPER (<texte>)
VALUE(<text>)

Tutoriel Excel DAX