Comment résoudre un problème DAX complexe

Niveau: Avancée

J'aidais quelqu'un sur le forum Power Pivot la semaine dernière avec une question de colonne calculée délicate. En outre, vous savez peut-être que je conseille toujours aux débutants de rester à l'écart des colonnes calculées. La plupart du temps, vous n'avez pas besoin de colonnes calculées car dans la plupart des cas, une mesure est un meilleur choix à utiliser (en savoir plus à ce sujet ici). À cette occasion, cependant, la formule requise est susceptible d'être complexe, les performances d'exécution d'une telle formule en tant que mesure peuvent être lentes, en plus du fait que le nombre de valeurs uniques possibles de la formule DAX est petit. Lorsque ces conditions sont remplies, une colonne calculée peut être une bonne solution.

Mon processus est plus important que ma solution

Je veux dire avant de commencer ça cet article de blog concerne le processus Je suis passé par une solution de travail. Je pense que le processus est beaucoup plus intéressant et beaucoup plus utile que la formule finale. En outre, quand tu regardes la formule finale, vous pourriez penser que vous ne comprenez même pas ce que fait la formule, et encore moins comment l'écrire. Mais voici le tournant: quand je regarde la formule finale, je pense la même chose. C'est pourquoi le processus d'écriture de cette formule est si important.

Voici la formule finale.

Si vous voulez apprendre à écrire une formule comme celle-ci, continue de lire.

=
CALCULER(
    MAXIMUM(Changements[nouvelle valeur]),
    FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres])),
    FILTRE(Changements, Changements[changer le type] = "Taper"),
    FILTRE(
        Changements,
        Changements[date de début]
            = CALCULER(
                MAXIMUM(Changements[date de début]),
                FILTRE(
                    CALCULABLE(
                        Changements,
                        FILTRE(Changements, Changements[ID membres] = AU PLUS TT(Changements[ID membres])),
                        FILTRE(Changements, Changements[changer le type] = "Taper")
                    ),
                    Changements[date de début] < AU PLUS TT(Changements[date de début])
                )
            )
    )
)

Le problème expliqué

Il y a un tableau de données (voir exemple ci-dessous). Le tableau contient des informations sur les changements dans le statut d'adhésion des membres d'un club. À n'importe quel moment, un membre a

• Un état (actif ou inactif)
• Un type (plein, partiel, limité)

Comme il “état” comme lui “Type” sont indiqués par un code. Les codes d'état et de type peuvent changer au fil du temps. Quand ceux-ci changent, un journal des modifications est créé montrant l'ancienne_valeur et la nouvelle_valeur. Une ligne dans le tableau équivaut à un changement.

Les points clés sont:

• Le journal des modifications (ligne) c'est peut-être pour un changement de état ou un changement dans le écrit comme indiqué dans le Changer le type colonne.
• Il existe une colonne Member_ID qui indique à quel membre appartient la ligne de modification.
• La date du changement est le start_date.

Cibler

Le but est de savoir ce que “état actuel” du code de type pour chaque membre. au moment où le nouveau changelog a été ajouté. En d'autres termes, au moment de la création de ce changelog, Ce client était-il membre à part entière, partielle ou limitée?
Donc, le problème est que je dois trouver le dernier “tapez le code” fixé avant la date de transaction actuelle. Un exemple clarifiera.

Exemple

Voici une copie filtrée de la table pour member_id 3100. Les transactions sont triées dans l'ordre start_date (quelle est la date de la transaction). Ce tableau contient ma formule de colonne calculée de travail finale “Taux actuel”

• Les premières 2 transactions (Lignes) s'est passé le 28/1/1998 (la date de début). A cette époque, il n'y avait pas d'entrée préexistante pour “Type” Oui, En tant que tel, la colonne calculée “Taux actuel” aller vide (regarder 1 en haut).
• La transaction dans la file d'attente 3 s'est passé le 22/3/2013 (la date de début). En ce moment, les “Type” pour ce client c'était “12” car c'était le dernier code défini comme new_value avant le 22/3/2013. Dans ce cas, les “Type” les 28/1/1998 comme il est montré dans 2 en haut. Par conséquent, ma colonne calculée renvoie 12 pour l'enregistrement 3 (regarder 2 en haut).
• Le registre 3 en fait changé la valeur "Type" de 12 une 14, donc les enregistrements suivants (4 une 7) devrait retourner la valeur 14. Et ils font ce que vous pouvez voir dans 3 en haut.

Une conception différente?

Avant de partager avec vous comment j'ai créé cette colonne calculée, il convient de noter que ce serait probablement plus facile à résoudre s'il y avait 2 tableaux séparés, un contenant les transactions de “Type” et un autre tableau contenant les transactions de “État”. Mettant ça de côté, c'est un grand défi comme c'est, et travailler sur des problèmes DAX difficiles vous aidera à devenir meilleur et plus fort: un vrai DAX Ninja.

Comment j'ai résolu le problème: pas à pas

Le début

J'avais une vision claire dans mon esprit de ce que je devais faire avant de commencer. Lors de la résolution de tout problème DAX, faut penser à “filtrer d'abord, évaluer la deuxième”. En d'autres termes, filtrer le tableau qu'ils me donnent pour qu'il ne contienne que ce dont j'ai besoin, alors prends les valeurs que je recherche.

Avec ça en tête, pour chaque enregistrement, nécessaire:

1. Vérifiez à quel membre appartenait l'enregistrement unique
2. Vérifiez la date de ce changement d'enregistrement
3. Filtrez l'ensemble du tableau pour:
   1. ce membre
   2. également pour tous les enregistrements qui étaient change_type = “taper”
   3. également pour tous les enregistrements antérieurs à la date d'enregistrement actuelle
4. Alors, une fois que j'ai eu le tableau filtré ci-dessus, Je devais trouver le dernier enregistrement par start_date, puis prendre le numéro de la colonne new_value

Paso 1. Commencer par n'importe quoi

Franchement, Je voulais juste écrire quelque chose pour m'aider à démarrer. Alors j'ai commencé avec quelque chose de simple. Je me suis demandé: “Combien de lignes chaque client a-t-il dans ce tableau?”

J'ai écrit cette colonne calculée.

=
CALCULER(
      PAYS(Changements),
      FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres]))
)

La ligne 4 C'est la clé. La ligne 4 dé…

“Allez mettre un filtre sur la table Changes où le member_id est égal à member_id de cette ligne". La fonction PREVIOUS est nécessaire car une colonne calculée a un contexte de ligne et la fonction FILTER aussi.

La syntaxe VAR

Si vous avez Excel 2016 o Power BI Desktop, vous pouvez utiliser la syntaxe VAR. Je pense que la syntaxe VAR est plus facile à écrire et à lire. Voici la même formule avec la syntaxe VAR.

=
VAR ThisMember = Modifications[ID membres]
REVENIR
    CALCULER(
         PAYS(Changements),
         FILTRE(Changements, Changements[ID membres] = Ce membre)
    )

J'espère que vous convenez que cette version VAR est plus facile à lire et à comprendre. Premier, définir une valeur pour la variable sur la ligne 2. Alors, vous pouvez utiliser cette variable dans vos formules sans avoir à vous soucier du contexte de la ligne interne et externe.

Bien, Je suis opérationnel et j'ai résolu le premier problème. J'ai écrit une formule qui détecte correctement le member_id actuel et filtre la table pour ce membre (comme vous pouvez le voir ci-dessous dans la colonne WIP).

Bien sûr, J'avais besoin d'utiliser COUNTROWS car il n'est pas possible de mettre un tableau dans une colonne. À sa place, J'ai utilisé COUNTROWS comme hack pour pouvoir “regarder” quelle est la taille de la table pour chaque membre. J'utilise COUNTROWS régulièrement lorsque je construis des formules complexes, car c'est un bon moyen de “regarder” la table virtuelle que je construis. Un autre point important ici est les données de test. Quand j'ai écrit cette formule de test pour la première fois, avait en fait 7 lignes dans l'exemple de table pour chaque client. Cela rendait très difficile de vérifier si la formule fonctionnait. Ensuite, avant de faire autre chose, Je suis revenu en arrière et j'ai modifié mes données de test afin que chaque membre ait un nombre différent de lignes. Alors je pourrais “regarder” que la formule fonctionnait. De bonnes données de test sont essentielles pour une écriture de formule efficace.

Paso 2. Combien de rangées de “Type” pour ce membre?

Travailler sur mon plan mental de haut niveau, J'ai décidé d'essayer de filtrer la table pour compter combien de lignes il y avait pour chaque client où l'enregistrement était change_type = "type". Voici ma nouvelle formule.

=
CALCULER(
     PAYS(Changements),
     FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres])),
     FILTRE(Changements, Changements[changer le type] = "Taper")
)

Comme tu peux le voir, je suis revenu à la syntaxe d'origine (péché VAR). Je viens d'ajouter un nouveau filtre dans change_type = “taper”. Il était facile de vérifier si cette formule fonctionnait en regardant les résultats.

Paso 3. Extraire quelque chose de plus utile

Jusqu'à présent, j'ai compté le nombre de lignes dans ces tables de test. Ensuite, maintenant que j'avais la bonne table à utiliser comme filtre, J'ai décidé d'essayer d'extraire la dernière date de début de cette table. C'était aussi simple que de changer la ligne 3 comme vous pouvez le voir ci-dessous.

 =
CALCULER(
     MAXIMUM(Changements[date de début]),
     FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres])),
     FILTRE(Changements, Changements[changer le type] = "Taper")
)

Et la nouvelle formule extrait désormais correctement la dernière date de début de cette table. N'oubliez pas que la table contient tout change_type = “taper” pour ce membre_id, donc cette date de début est la date de la dernière transaction de “Type” trouvé.

Paso 4. Convertir le 2 filtres dans une seule table

La formule ci-dessus a 2 fonctions de filtrage. J'ai vraiment besoin que ce soit une seule table pour pouvoir l'utiliser. Il y a quelques façons de le faire, mais j'ai décidé de le convertir en un tableau comme suit. Notez que j'utilise toujours COUNTROWS donc je peux valider qu'il fonctionne toujours.

=
PAYS(
    CALCULABLE(
          Changements,
          FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres])),
          FILTRE(Changements,Changements[changer le type] = "Taper")
    )
)

Les rangées 3 une 7 maintenant ils sont un “tableau” que je peux utiliser au fur et à mesure. À sa place, J'aurais pu utiliser une seule fonction FILTER avec plusieurs filtres en utilisant &&.

Paso 5. Obtenez la dernière date avant cette date de transaction

Ce tableau contient toutes sortes de transactions. Mais Je ne veux pas Tout le monde type d'opérations, je recherche la dernière transaction qui a eu lieu avant de cette opération en cours. En d'autres termes, Je dois filtrer ce tableau pour supprimer tout type de transaction qui a eu lieu à la date de ou après est transaction. J'ai décidé de revenir à la syntaxe VAR ici pour faciliter l'écriture de cette formule.

=
VAR thisStartDate = Modifications[date de début]
REVENIR
    CALCULER(
         MAXIMUM(Changements[date de début]),
         FILTRE(
             CALCULABLE(
                   Changements, 
                   FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres])),
                   FILTRE(Changements, Changements[changer le type] = "Taper")
             ),
             Changements[date de début] < thisStartDate
         )
     )

Notez que les lignes 7-11 ils sont le tableau que j'ai produit à l'étape 4. J'ai enveloppé cela dans un autre FILTRE pour supprimer tous les enregistrements à ou après la date d'enregistrement actuelle, enregistrer uniquement les journaux qui se sont produits avant la date actuelle du journal des modifications. Donc, les rangées 6 une 13 ils retournent une table contenant tous les enregistrements de « type’ ce qui s'est passé avant ce record. Ensuite, j'utilise l'instruction CALCULATE pour renvoyer la date du dernier type de transaction. Comme vous le verrez dans le tableau résultant ci-dessous, la colonne WIP renvoie désormais des blancs pour tout enregistrement qui n'a pas d'enregistrement de 'type’ pour une période antérieure.

Paso 6. Extraire la valeur de registre de “Type” antérieur

Maintenant que je connais la date de l'enregistrement de 'type’ C'est Correct, Je peux procéder à l'extraction de la valeur de cet enregistrement de la colonne new_value. voici ma formule.

=
VAR thisStartDate = Modifications[date de début]
VAR datePourUtiliser =
    CALCULER(
        MAXIMUM(Changements[date de début]),
        FILTRE(
           CALCULABLE(
              Changements, 
              FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres])),
              FILTRE(Changements, Changements[changer le type] = "Taper")
           ),
           Changements[date de début] < thisStartDate)
        )
REVENIR
    CALCULER(
         MAXIMUM(Changements[nouvelle valeur]),
         FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres])),
         FILTRE(Changements, Changements[changer le type] = "Taper"),
         Changements[date de début] = dateÀUtiliser
    )

Remarquez comment j'ai pris le résultat de la formule à l'étape 5 et l'a transformé en un autre VAR (Lignes 3 une 14). Alors, J'ai écrit une nouvelle formule (Lignes 16 une 21 en haut) pour extraire la valeur dont j'ai besoin après avoir filtré le member_id (ligne 18), el change_type (ligne 19) et la date correcte de la transaction (ligne 20).

Une dernière complication

J'ai eu un dernier problème que j'ai rencontré. El OP et http://powerpivotforum.com.au utilisait Excel 2010, donc je ne pourrais pas fournir la formule ci-dessus (Exceller 2010 ne prend pas en charge la syntaxe VAR). J'ai donc dû supprimer la syntaxe VAR. J'ai d'abord supprimé le premier VAR (ligne 2 de l'étape 6 antérieur) de la manière suivante. Notez comment j'ai dû utiliser AVANT sur la ligne 11 pour gérer le contexte de la ligne.

=
VAR datePourUtiliser =
    CALCULER(
        MAXIMUM(Changements[date de début]),
        FILTRE(
            CALCULABLE(
                Changements,
                FILTRE(Changements, Changements[ID membres] = PLUS TT( Changements[ID membres])),
                FILTRE(Changements, Changements[changer le type] = "Taper")
            ),
            Changements[date de début] < PLUS TÔT(Changements[date de début])
        )
    )
REVENIR
    CALCULER(
        MAXIMUM(Changements[nouvelle valeur]),
        FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres])),
        FILTRE(Changements, Changements[changer le type] = "Taper"),
        FILTRE(Changements, Changements[date de début] = dateÀUtiliser)
    )

Enfin, j'ai supprimé le deuxième VAR de l'étape 6 de la manière suivante. j'ai pris les lignes 3-13 de la formule ci-dessus et j'ai remplacé dateToUse ci-dessus sur la ligne 19 avec cette formule.

=
CALCULER(
    MAXIMUM(Changements[nouvelle valeur]),
    FILTRE(Changements, Changements[ID membres] = PLUS TT(Changements[ID membres])),
    FILTRE(Changements, Changements[changer le type] = "Taper"),
    FILTRE(
        Changements,
        Changements[date de début]
            = CALCULER(
                MAXIMUM(Changements[date de début]),
                FILTRE(
                    CALCULABLE(
                        Changements,
                        FILTRE(Changements, Changements[ID membres] = AU PLUS TT(Changements[ID membres])),
                        FILTRE(Changements, Changements[changer le type] = "Taper")
                    ),
                    Changements[date de début] < AU PLUS TT(Changements[date de début])
                )
            )
    )
)

Après avoir fait ce changement, notez que cette fois sur les lignes 14 Oui 17 J'ai dû modifier la formule pour bien gérer le contexte de la ligne imbriquée, mais cette fois, j'ai dû me référer à EARLY au lieu de EARLY. C'est parce qu'il y a 3 ensembles imbriqués de contexte de ligne. La première est la colonne calculée, le second est le FILTRE de la ligne 6 et le troisième est le FILTRE de la rangée 11.

Un dernier mot

Le point clé de cet article est que la formule finale ci-dessus est difficile à lire et à comprendre. Vous pensez peut-être que cette formule est écrite en commençant par le haut et en descendant, Mais cela est loin de la vérité. Les formules DAX complexes rarement (si jamais) sont écrits de haut en bas. L'astuce pour écrire des formules DAX complexes est de décomposer le problème en morceaux et de résoudre chaque pièce du puzzle étape par étape.

Voici le sample_workbook et voici toutes les formules que j'ai écrites.

Avez-vous une meilleure solution?

Je sais qu'il y a des DAX Ninjas talentueux qui lisent mon blog (Jess, Owen de nommer 2). j'aime toujours apprendre, donc si vous avez une formule / meilleure solution ou approche alternative intéressante que celle-ci, J'aimerais beaucoup le voir. Postez dans les commentaires.