J'ai enseigné la formation Power BI dans diverses universités par le passé 6 mois et certains des participants m'ont posé la même question. « Quelle est la meilleure façon d'ajouter des semestres à un calendrier afin que vous puissiez analyser les données des étudiants par semestre?“ Il y a plusieurs façons de résoudre ce problème. Dans cet article, je veux partager avec vous un moyen de le résoudre en utilisant DAX dans Power BI. je vais utiliser la base de données AventureTravaux pour démonstration. Pour garder la démo plus réaliste, je vais renommer certaines tables (je ne changerai pas les données).
- La table Clients s'appellera Etudiants
- Le tableau des ventes s'appellera Inscriptions par sujet.
- La table Territoire s'appellera Campus
Pas un échange parfait d'un, mais je pense que cela servira le but. Je ne changerai pas les données réelles, mais je vais utiliser les données comme décrit ci-dessus. Je sais que les données d'inscription des étudiants sont différentes de celles-ci, mais je n'ai pas ces données disponibles; cette procuration devrait illustrer assez bien le point de l'article.
Le rapport final ressemble à ceci.
Comme c'est ma pratique habituelle, ci-dessous, je vais vous montrer comment le faire étape par étape. La technique que je vais utiliser est essentiellement Encerclé à DAX que j'ai couvert dans un article de blog précédent.
Exemples de données
Pour commencer, J'ai créé un tableau de données maître avec les dates de début et de fin du semestre; J'ai appelé cette table SemestreDates. J'ai supposé qu'il y aurait 2 semestres en un an. Bien sûr, certaines universités auront même 3 O 4 semestres en un an; Le principe est le même. J'ai nommé les deux semestres d'une année comme S1 et S2. J'ai également ajouté une colonne YYYYSS pour créer une colonne unique de semestre et d'année. Finalement, J'ai ajouté une colonne d'ID de semestre pour faciliter l'écriture des formules DAX. Notez que cette dernière colonne est un entier qui identifie de manière unique chaque semestre et est incrémenté de 1 pour chaque semestre qui passe.
Ceci est un tableau simple que tout le monde peut créer dans Excel. Une fois créé, cela devient la table principale qui peut simplement être maintenue chaque année au fur et à mesure que les nouvelles dates du semestre sont affichées.
Chargement de la table des dates du semestre dans le modèle de données Power BI
J'ai chargé la table SemesterDates dans le classeur AdventureWorks Power BI. Dans ce cas, Je ne connecterai la table à aucune autre table du modèle de données. Le but des relations dans Power BI est de propager filtre un tableau à un autre; je n'ai pas besoin de faire ça, donc, pas de relations. J'ai également décidé de masquer le tableau pour qu'il ne s'affiche pas dans la vue Rapport. C'est parce que je ne l'utiliserai que pour écrire les formules DAX requises (colonnes calculées) requis à des fins de déclaration. Ceci est ma vue relationnelle du modèle de données.
Ajouter des colonnes calculées à la table du calendrier
je le recommande toujours, en cas de doute, ajouter des mesures et non des colonnes calculées. Mais il y a toujours des exceptions à cette règle, comme je l'ai expliqué dans mon article de blog. Dans ce cas, nous ajoutons les colonnes calculées à une table de recherche (Calendrier) pour l'améliorer et Le Tables de recherche ils sont normalement plus petits (moins de lignes) Oui, donc, l'impact de la taille des colonnes calculées n'est pas si mal. En outre, Je veux utiliser le résultat pour diviser mes données, et une mesure ne peut pas faire ça; j'ai besoin d'une colonne.
J'ai ajouté trois colonnes calculées AAAAAA, Semestre Oui ID semestre en utilisant les formules DAX suivantes indiquées ci-dessous. J'ai utilisé la syntaxe VAR pour que les formules soient faciles à comprendre. Ces formules sont essentiellement la technique de baguage dans DAX.
YYYYSS = VAR CurrentDate="Calendrier"[Date]
Résultat VAR = CALCULER(VALEUR SÉLECTIONNÉE(SemestreDates[AAAAAA]),
Date actuelle >=Dates du semestre[Partir de la date],
Date actuelle <=Dates du semestre[À ce jour]
)
RETOUR SI(Résultat = BLANC(), "Vacances", Résultat)
Semestre = WAS CurrentDate ="Calendrier"[Date]
Résultat VAR = CALCULER(VALEUR SÉLECTIONNÉE(SemestreDates[Semestre]),
Date actuelle >=Dates du semestre[Partir de la date],
Date actuelle <=Dates du semestre[À ce jour]
)
RETOUR SI(Résultat = BLANC(), "Vacances", Résultat)
ID de semestre = VAR CurrentDate ="Calendrier"[Date]
Résultat VAR = CALCULER(VALEUR SÉLECTIONNÉE(SemestreDates[Identifiant du semestre]),
Date actuelle >=Dates du semestre[Partir de la date],
Date actuelle <=Dates du semestre[À ce jour]
)
RETOUR SI(Résultat = BLANC(), 0, Résultat)
L'astuce avec les formules ci-dessus est de faire ce qui suit (en tenant compte du fait qu'il s'agit d'une colonne calculée)
- Prendre la date pour chaque ligne du tableau du calendrier (la formule s'exécute une ligne à la fois, c'est-à-dire, a un contexte de ligne)
- Poussez un filtre sur le tableau semestriel afin que le tableau semestriel affiche une seule ligne après l'application du filtre.
- La seule ligne doit être la ligne à ou après la date de début, et aussi au plus tard à la date de fin.
- Une fois que le tableau semestriel n'a qu'une seule ligne (après l'application des filtres), extraire la valeur unique de l'une des autres colonnes de la table semestrielle et l'enregistrer dans ma nouvelle colonne calculée.
Cela fonctionne car les plages de dates dans le tableau des semestres s'excluent mutuellement (il n'y a pas de périodes de chevauchement où une date donnée est dans 2 semestres).
Définir des mesures pour informer les statistiques de données
Nous sommes maintenant prêts à définir les mesures nécessaires pour éclairer les informations sur les données. Et comme c'est la pratique habituelle, j'ai d'abord ajouté un visuel de tableau au rapport avec le champ 'Calendrier'[AAAAAA] puis continuez à ajouter chaque mesure pour vous assurer que mes formules DAX sont correctes.
La première mesure que j'ai écrite est de compter le nombre d'élèves. Rappelles toi, j'utilise AdventureWorks ici en fait, donc pas un substitut parfait pour les inscriptions réelles. J'utilise la date d'achat d'AdventureWorks comme proxy pour une date de transaction au cours d'un semestre. Cependant, Le principe est le même.
Nombre total d'étudiants ce semestre = DISTINCTCOUNT(Inscriptions[CléÉtudiant])
Ensuite, je définis la mesure pour compter le nombre d'étudiants au semestre précédent.
Nombre total d'étudiants Précédent. Semestre = VAR Semestre Actuel = VALEUR SÉLECTIONNÉE('Calendrier'[Identifiant du semestre])
VAR Semestre précédent = Semestre actuel - 1
Résultat VAR = CALCULER([Nombre total d'étudiants ce semestre],
TOUS('Calendrier'),
'Calendrier'[Identifiant du semestre] = Semestre précédent)
RETOUR Résultat
Nous pouvons définir Chg vs semestre précédent comme suit.
Chg vs Préc. Semestre = VAR Semestre Actuel = VALEUR SÉLECTIONNÉE('Calendrier'[Identifiant du semestre])
VAR Semestre précédent = Semestre actuel - 1
Résultat VAR = SI(Semestre précédent <> 0,
[Nombre total d'étudiants ce semestre] - [Nombre total d'étudiants Précédent. Semestre]
)
RETOUR Résultat
Nous avons enfin obtenu le rapport souhaité.
Voici l'exemple de classeur que j'ai utilisé dans cet article de blog.
Un dernier mot
Comme je l'ai mentionné au début, il y a plus d'une façon de résoudre ce problème, y compris l'utilisation de Requête de puissance au lieu de DAX. L'inconvénient de l'utilisation de DAX (cette méthode) est que vous devez écrire les colonnes calculées dans chaque nouveau classeur que vous créez. Indépendamment, est un moyen simple et utile de résoudre le problème.
