Fonctions

Conditions

HASONEVALUE

La matrice (ou table ou graphique...) n’affichera aucune données chiffrée de la mesure tant que le champ ne sera pas présent, en ligne ou en colonne.

IF (
    HASONEVALUE ( 'Date'[Calendar Year] ),
    True,
    False
)

Tant que le champ Calendar Year de la table Date n’est pas présent dans la vignette, n’affiche rien.

Regroupements

ADDCOLUMNS/SUMMARIZE (avant 2016)

Crée un résumé de <table> (supprime les doublons) avec des colonnes <group_by_column>, et ajoute des colonnes <column_name> contenant des calculs <expression>. Ne pas ajouter de colonnes calculées dans SUMMARIZE, mais dans ADDCOLUMNS (performance et complexité)

ADDCOLUMNS(
    SUMMARIZE(
        <table>,
        <group_by_column> ),
    <column_name>, CALCULATE( <expression> )
)

ADDDCOLUMNS s’execute dans le seul contexte de ligne, donc on doit indiquer CALCULATE pour ajouter un contexte de filtre.

SUMMARIZECOLUMNS (à partir de 2016)

[Voir l’auto existe pour cette fonction]

A partir d’Excel 2016 et Power BI, on peut remplacer ADDCOLUMNS/SUMMARIZE par SUMMARIZECOLUMNS.

Crée un résumé des colonnes <group_by_column> et ajoute des colonnes <column_name> contenant des calculs <expression>.

SUMMARIZECOLUMNS (
    <group_by_column>,
    <column_name>, <expression> )
)

GROUPBY / CURRENTGROUP

Remplace SUMMARIZECOLUMNS.

GROUPBY (
    <table>,
    <group_by_column>,
    <column_name>, <expression> )
)

Exemple :

GROUPBY(
   <table>,
    <group_by_column>,
    <column_name>,
   SUMX(
      CURRENTGROUP(),
      <column>))

Ressources (ES) :

VALUES / DISTINCT

Retourne les valeurs uniques d’une colonne (VALUES retourne les vides) dans le contexte de filtre.

VALUES ( <column_name> )

[Exemple] Lister les valeurs par ligne

Chaque Manager a plusieurs fois le même Month.

SELECTEDVALUE(
    Sales[Month],
    CONCATENATEX(
        VALUES(
            Sales[Month]
        ),
    Sales[Month],
    ","
    )
)

Résultat :

Relations

Pas de relation dans le modèle, ou relation inactive

Selon disponibilité dans les versions, utiliser d’abord TREATAS, sinon INTERSECT sinon dans tous les cas FILTER.

Table résultante pour l’exemple “Promotion” (une table Date et une table Promotion, promotion selon la catégorie et selon l’année :

SUMMARIZECOLUMNS(
    Promotion[Promotion]
    “Mesure CALCULATE”, <Mesure Calculate>
)

TREATAS (pas de relation)

[Filtered Measure] :=
CALCULATE (
    <target_measure>,
    TREATAS (
        VALUES ( <lookup_granularity_column> ).
        <target_granularity_column>
    )
)

Retourne une table filtrée sur les <target_granularity_column> contenant les <lookup_granularity_column>.

Exemple :

CALCULATE (
    [Sales Amount],
    TREATAS (
        { ( 2019, 12) , (2020, 1) },
        ‘Date’[Year Number],
        ‘Date’[Month Number]
    )
)

Calcule l’expression Sales Amount, en filtrant l’année / mois sur 2019 / 12 ou sur 2020 / 1.

Exemple “Promotion” :

CALCULATE (
    [Sales Amount],
    TREATAS (
        SUMMARIZE ( Promotion, Promotion [Category], Promotion[Year] )
        ‘Product’[Category],
        ‘Date’[Calendar Year Number]
    )
)

INTERSECT (pas de relation - avant Fév. 2017)

[Filtered Measure] :=
CALCULATE (
    <target_measure>,
    INTERSECT (
        ALL ( <target_granularity_column> ),
        VALUES ( <lookup_granularity_column> )
    )
)

Exemple "Promotion” :

CALCULATE (
    [Sales Amount],
    INTERSECT (
        CROSSJOIN (
            ALL ( 'Product'[Category] ),
            ALL ( 'Date'[Calendar Year Number] )
        )
        SUMMARIZE ( Promotion, Promotion [Category], Promotion[Year] )
    )
)

CROSSJOIN Avec FILTER

Exemple “Promotion”

CALCULATE (
    [Sales Amount],
    FILTER (
        CROSSJOIN (
            ALL ( 'Product'[Category] ),
            ALL ( 'Date'[Calendar Year Number] )
        )
        CONTAINS (
            Promotion,
            Promotion[Category], 'Product'[Category],
            Promotion[Year], ‘Date’[Calendar Year Number]
        )
    )
)

USERELATIONSHIP

Si relation entre les 2 tables, préférez USERELATIONSHIP :

= CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(InternetSales[ShippingDate], DateTime[Date]))

Calcule la somme des SalesAmount en utilisant la relation entre les tables InternetSales et DateTime. La relation doit exister dans le modèle, même inactive.

Relations dans le modèle

CROSSFILTER

Product Category 1 → oo Product SubCategory 1 → oo Product 1 → oo Sales

Dans un matrice qui affiche le Product Name et la somme des Sales Amoung, on ajoute la Product Category :

Retourne la Category d’un produit :

IF (
    NOT EMPTY ( Sales ),
    CALCULATE(
        SELECTEDVALUE ( 'Product Category'[Category] ),
        CROSSFILTER (
            'Product'[ProductSubcategoryKey],
            'Product Subcategory'[ProductSubcategoryKey],
            BOTH
        ),
        CROSSFILTER (
            'Product Subcategory'[ProductCategoryKey],
            'Product Category'[ProductCategoryKey],
            BOTH
        )
    )
)

LOOKUPVALUE

LOOKUPVALUE(
    <result_columnName>,
    <search_columnName>,
    <search_value>
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]
)

Recherche la <search_value> dans la colonne <search_columnName> et retourne <result_columnName> quand trouvé, sinon <alternateResult>.

Exemple :

CHANNEL = LOOKUPVALUE('Sales Order'[Channel],'Sales Order'[SalesOrderLineKey],[SalesOrderLineKey])

RELATED(<column>)

Retourne la valeur de <column>, en utilisant la relation entre la table appelante et la table contenant la colonne.

Exemple :

Si relation entre Sales Order et Order :

CHANNEL = RELATED('Sales Order'[Channel])

Utilise la relation entre

RELATEDTABLE

Filtres

On parle d’un filtre appliqué à :

une matrice / un Tableau Croisé Dynamique,
un graphique

dans

Excel
Power BI (on cré le DAX avec Desktop et on utilise les calculs DAX dans la version en ligne)

en appliquant :

un segment
un filtre dans le graphique/matrice (PBI)
un filtre dans la page, dans le rapport (PBI)
une interaction dans le graphique/la matrice

Ce ou ces filtres seront appliqués à la mesure.

CALCULATE

CALCULATE (
    <expression>,
    <filter1>,
    ...
    <filterN>
)

<filter> peut être :

une valeur logique (= qui renvoi Vrai ou Faux)
une expression de table, qui supprime les filtres déjà appliqués sur les colonnes retournées par l’expression de table.

[Sales2006] := 
CALCULATE ( 
    SUM ( Sales[SalesAmount] ),
    OrderDate[Year] = 2006
)

Supprime le filtre appliqué sur la colonne Year de la table OrderDate.
Conserve tous les autres filtres qui seraient appliqués (sur le table Sales ou la table OrderDate ou toutes les autres tables qui filtreraient la mesure)
Filtre la table OrderDate quand la colonne Year = 2006
Somme la colonne SalesAmount

Ce qui donne :

2005	600
2006	600
2007	600

FILTER

Itérateur, donc évalue chaque ligne individuellement

[Sales2006] := 
CALCULATE ( 
    SUM ( Sales[SalesAmount] ),
    FILTER (
      OrderDate,
      OrderDate[Year] = 2006
  )
)

2005
2006	600
2007

Cf. DAX : Évitez d’utiliser FILTER comme argument de filtre - Power BI.

ALL

[Sales2006] := 
CALCULATE ( 
    SUM ( Sales[SalesAmount] ),
    FILTER (
        ALL ( OrderDate[Year] ),
        OrderDate[Year] = 2006
    )
)

Supprime les filtres qui serait appliqués aux colonnes de la table OrderDate, y compris la colonne Year (condition suivante).
Filtre la table OrderDate quand la colonne Year = 2006
Somme la colonne SalesAmount

VALUES / DISTINCT

[Sales2006ifSelected] := 
CALCULATE ( 
    SUM ( Sales[SalesAmount] ),
    FILTER (
        VALUES ( OrderDate[Year] ),
        OrderDate[Year] = 2006
    ) 
)

SELECTEDVALUE

Retourne une seule valeur quand le filtrage produit une seule valeur, sinon retourne le résultat alternatif.

SELECTEDVALUE(<column_name>, <alternate_result>)

SELECTEDVALUE(<column_name>, ERROR ("Select ONE value") )

Equivalent de :

IF (
    HASONEVALUE ( 'Product'[Class] ),
    VALUES ( 'Product'[Class] )
)

VALUES retourne les valeurs uniques d’une colonne dans un contexte de filtre.

REMOVEFILTERS

“Sugar syntax” de ALL comme filtre dans CALCULATE. Ces 2 formules renvoient le même résultat :

Total Sales All Products = CALCULATE([Total Sales], REMOVEFILTERS(Products))

Total Sales All Products = CALCULATE([Total Sales], ALL(Products))

Syntaxes

REMOVEFILTERS <table>
REMOVEFILTERS <column>
REMOVEFILTERS

Exemples

Supprime tous les filtres appliqués à la table Products :

Total Sales All Products = 
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(Products)
)

Supprime le filtre appliquée à la colonne Color de la table Products :

Total Sales All Coloured Products = 
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(Products[Color])
)

Supprime les filtres des colonnes Color et Category de la table Products :

Total Sales All Colours and Category Products =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(Products[Color], Products[Category])
)

Supprime tous les filtres :

Total Sales of Everything =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS()
)

KEEPFILTERS

Conserve le filtre dans un filtre de CALCULATE.

Syntaxes

KEEPFILTERS <table>
KEEPFILTERS <column>

Exemples

Conserve le filtre sur la colonne Color de la table Products.

Black Sales with KEEPFILTERS = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    KEEPFILTERS(Products[Color] = "Black")
)

Voici l’équivalent avec VALUES :

OnlyRed_Values =
CALCULATE (
    [SalesAmount],
    FILTER(
        VALUES( Products[Color] ),
        Products[Color]= "Red"
    )
)

Résultat :

ALLSELECTED

Bonnes pratiques de filtre

Cf. Using ALLEXCEPT vs ALL VALUES

Au lieu de :

Pourcentage dans le continent =
CALCULATE(
    [Total Sales],
    ALLEXCEPTS ( Costumer, Costumer[Country] )
)

Suppose que le champ Continent soit présent dans la vignette. ALLEXCEPTS commence par supprimer TOUS les filtres sur Costumer.

Supprime tous les filtres de la table Customer puis applique un filtre :

Pourcentage dans le continent =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS ( Customer ),
    VALUES ( Costumer[Continent] )
)

Total Sales sera toujours calculé par rapport à Continent.

Avec plusieurs colonnes :

Supprime tous les filtres de la table Customer puis applique un filtre de plusieurs colonnes :

Pourcentage dans le continent =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS ( Customer ),
    SUMMARIZE ( Costumer, Costumer[Continent], Costumer[...] )
)

Ou CROSSJOIN (VALUES(), VALUES()) si colonnes de différentes tables.

Agrégations

MAXX

ProductRank =
MAXX ( Product , [SalesAmount]

Bob	600
John	600
Vance	600

RANKX

DEFINE
    VAR BrandsAndSales =
        ADDCOLUMNS (
            VALUES ( 'Product'[Brand] ),
            "@Amt", [Sales Amount]
        )
EVALUATE
ADDCOLUMNS (
    BrandsAndSales,
    "Rank",
        RANKX (
            BrandsAndSales,
            [@Amt]
        )
)
ORDER BY [@Amt] DESC

Exemples complets

Différence avec période précédente

Sales PM :=
VAR CurrentYearMonth = SELECTEDVALUE ( 'Date'[Year Month Number] )
VAR PreviousYearMonth =
    CALCULATE (
        MAX ( 'Date'[Year Month Number] ),
        ALLSELECTED ( 'Date' ),
        KEEPFILTERS ( 'Date'[Year Month Number] < CurrentYearMonth )
    )
VAR Result =
    CALCULATE (
        [Sales Amount],
        'Date'[Year Month Number] = PreviousYearMonth,
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

Et au final :

Sales Diff PM :=
VAR SalesCurrentMonth = [Sales Amount]
VAR SalesPreviousMonth = [Sales PM]
VAR Result =
    DIVIDE (
        SalesCurrentMonth - SalesPreviousMonth,
        ( NOT ISBLANK ( SalesCurrentMonth ) ) * ( NOT ISBLANK ( SalesPreviousMonth ) )
    )
RETURN
    Result

% Sales Diff PM =
DIVIDE (
    [Sales Diff PM],
    [Sales PM]
)