Microsoft Power Bi

llo n ti an

É

ch

Un compendium de techniques pour Microsoft Excel BI & Microsoft Power BI V0.11 Révision 98 (2020-05-07) Vincent Isoz, Daname Kolani, Laurianne Demarlière

Vincent ISOZ

Le but de ce fichier est de parcourir avec des exemples simples et concrets le maximum de fonctions DAX de Power BI permettant de bien comprendre l'utilité de chacune (lorsque associée à d'autres fonctions ou pas) et de se préparer aussi à la certification Exam 70-778 sur Power BI. L'idée étant de produire une multitude de petits MWE (Minimal Working Example).

llo n

Chaque exemple est donné avec un petit résumé fixant l'objectif à atteindre et à la page suivante, la résolution avec la capture d'écran de la formule DAX dans la barre de formule et le résultat tel qu'il apparaît dans le tableau de bord ou à l'écran. Chaque exercice doit être fait dans une feuille Power BI à part!

Le participant devra s'aider de la référence en ligne suivante pour faire les exercices: https://msdn.microsoft.com/en-us/library/ee634396.aspx On rappelle :

ti

1. Qu'il n'est pas possible de changer le typage des colonnes à ce jour dans des mesures DAX. Donc pour les colonnes numériques contenant des erreurs ou des textes, on sera obligé de d'abord créer des colonnes en DAX qui effectuent le nettoyage avant de pouvoir faire de mesures avec en DAX!!!!!!!!!!!!!

É

ch

an

2. Donc qu'il n'est pas possible de créer des mesures DAX renvoyant des images pour faire des KPI et que vous serez obligé de passer par la création de colonnes DAX!

Microsoft Power BI / Microsoft Excel BI

2/950

Vincent ISOZ

1. Table des matières Table des matières................................................................................................................. 3 À propos du rédacteur ........................................................................................................ 12 Avertissement ...................................................................................................................... 14 Votre avis nous intéresse! ................................................................................................... 15 Préambule ............................................................................................................................ 16 Copyright ............................................................................................................................. 17 Médiagraphie (liens) ........................................................................................................... 18 Introduction ......................................................................................................................... 21

llo n

1. 2. 3. 4. 5. 6. 7. 8.

Modélisation de bases de données (schémas en étoile ou flocon) .......................................................... 35

9. 10. 11. 12. 13. 14.

Nouveautés ........................................................................................................................... 36 Licences ................................................................................................................................ 40 Limites.................................................................................................................................. 45 Meilleures Pratiques (best practices) ................................................................................ 46 Moteurs VertiPaq et XVelocity.......................................................................................... 48 Data Munging ...................................................................................................................... 52

ti

Data Input ............................................................................................................................................... 53 Data sources............................................................................................................................................ 54

Tables manuelles ................................................................................................................................................ 54 Tables DAX ....................................................................................................................................................... 56 Microsoft SharePoint ......................................................................................................................................... 61

an

Bibliothèque d'images (Asset Library) ............................................................................................................................ 61 Liste d'images (liste personnalisée avec colonne Image) ................................................................................................ 66

Microsoft Excel .................................................................................................................................................. 70 Import d'un fichier Microsoft Excel stocké sur SharePoint ............................................................................................. 70 Import (fusion) automatique de toutes les feuilles (incluant les futures) d'un classeur ................................................... 75 Import contenu bibliothéques SharePoint ....................................................................................................................... 78

JSON .................................................................................................................................................................. 87 Microsoft SQL Server ........................................................................................................................................ 92 DirectQuery .................................................................................................................................................................... 94 Query Folding ................................................................................................................................................................. 97

ch

OData ............................................................................................................................................................... 101 Dataflow........................................................................................................................................................... 106 Streaming Dataset ............................................................................................................................................ 112

Import vs DirectQuery vs Live Connection.......................................................................................... 126 Data Flows (flux de données) ............................................................................................................... 129 Gateways .............................................................................................................................................. 130

15. Refresh (mises à jour des données).................................................................................. 140 Mises à jour des tables dans Power Query ........................................................................................... 141 Mises à jour des tables dans Power BI Desktop ................................................................................... 142

É

Mises à jour manuelle de toutes les tables ....................................................................................................... 142 Mises à jour manuelle de certaines tables ........................................................................................................ 142 Mises à jour automatique ................................................................................................................................. 143 Mises à jour incrémentielles............................................................................................................................. 145

Mises à jour dans Power BI Service ..................................................................................................... 146 Mises à jour manuelles ..................................................................................................................................... 146 Mises à jour automatiques................................................................................................................................ 146

16. Data Mashup (Power Query) ........................................................................................... 147 Commentaires M .................................................................................................................................. 148

Microsoft Power BI / Microsoft Excel BI

3/950

Vincent ISOZ

ch

an

ti

llo n

Icônes Power Query.............................................................................................................................. 149 Lister toutes les fonctions ..................................................................................................................... 150 Tri sur colonnes multiples .................................................................................................................... 154 Construction d'un VLOOKUP approximatif ........................................................................................ 157 Utiliser des variables ............................................................................................................................ 168 Fusion de feuilles multiples d'un unique fichier Microsoft Excel ........................................................ 169 Traîtement batch d'un dossier de fichiers Microsoft Excel avec feuilles identiquement nommées ..... 170 Fusion de fichiers Microsoft Excel d'un dossier avec feuille unique non identiquement nommées .... 182 Fusionner un dossier avec problèmes d'en-têtes de colonnes ............................................................... 184 Ajouter la date de dernières mise à jour sur un rapport ........................................................................ 188 Supprimer automatiquement toutes les lignes après une ligne particulière .......................................... 191 Création d'une Table de calendrier simple avec durée en M ................................................................ 194 Création d'une Table de calendrier avec étendue fixe en M ................................................................. 196 Création d'une Table de calendrier avec variable en M ........................................................................ 197 Création d'une Table de calendrier complète en M .............................................................................. 198 Filtre dynamique en M ......................................................................................................................... 201 Création d'une Fonction simple en M ................................................................................................... 203 Création d'une Fonction avec arguments optionnels en M ................................................................... 204 Création d'une Fonction avec arguments optionnels et typés en M ...................................................... 205 Fonction de nettoyage d'espaces (pour Power Query Excel seulement!) ............................................. 206 Création d'une Liste numérique en M ................................................................................................... 211 Création d'une Séquence numérique en M ........................................................................................... 212 Création d'une Séquence numérique en M avec pas spécifique ........................................................... 213 Création d'une Liste statique en M ....................................................................................................... 214 Création d'une Table statique en M ...................................................................................................... 216 Création d'une Table dynamique en M ................................................................................................. 218 Création d'un simple colonne de valeurs aléatoires .............................................................................. 220 Ajout d'une colonne de valeurs aléatoires numériques ......................................................................... 221 Création d'une colonne de valeurs aléatoires catégorielles................................................................... 225 Échantillonnage aléatoire simple .......................................................................................................... 226 Traitements des dates............................................................................................................................ 231 Création d'un paramètre pour chemin de fichiers (ou serveurs) ........................................................... 232 Importer le fichier du jour..................................................................................................................... 240 Compter les mots .................................................................................................................................. 242 Extraire les premiers/derniers de groupes............................................................................................. 255 Numéroter des groupes ......................................................................................................................... 262 Fusionner de multiples lignes en une seule ligne ................................................................................. 270 Lag de colonnes .................................................................................................................................... 275 Fuseaux horaires ................................................................................................................................... 278

17. DAX (Data Analysis Expression) ..................................................................................... 282 Reverse Query (DAX) .......................................................................................................................... 283

É

Exemple 1.: Requête EVALUATE .................................................................................................................. 284 Solution ......................................................................................................................................................................... 286

Exemple 2.: Requête ALL................................................................................................................................ 293 Exemple 3.: Requête SELECTCOLUMNS avec table unique......................................................................... 297 Exemple 4.: Requête VALUES........................................................................................................................ 299 Exemple 5.: Requête DISTINCT ..................................................................................................................... 302 Exemple 6.: Requête SELECTCOLUMNS( ) + DISTINCT( ) + AVERAGE( ) + ROUND( ) ....................... 305 Exemple 7.: Requête CALCULATETABLE ................................................................................................... 306 Exemple 8.: Requête FILTER .......................................................................................................................... 309

Microsoft Power BI / Microsoft Excel BI

4/950

Vincent ISOZ

llo n

Exemple 9.: Requête SAMPLE........................................................................................................................ 310 Exemple 10.: Requête SUMMARIZE ............................................................................................................. 311 Exemple 11.: Requête SUMMARIZE avec tables multiples ........................................................................... 315 Exemple 12.: Requête GROUPBY et CURRENTGROUP ............................................................................. 319 Exemple 13.: Requête SUMMARIZECOLUMNS .......................................................................................... 320 Exemple 14.: Requête SUMMARIZECOLUMNS et IGNORE ...................................................................... 321 Exemple 15.: Requête TOPN ........................................................................................................................... 322 Exemple 16.: Requête RANK.EQ .................................................................................................................... 324 Exemple 17.: Requête ROW ............................................................................................................................ 325 Exemple 18.: Requête SELECTCOLUMNS avec RELATED ........................................................................ 328 Exemple 19.: Requête GENERATE avec RELATEDTABLE ........................................................................ 332 Exemple 20.: Requête GENERATEALL avec RELATEDTABLE ................................................................ 336 Exemple 21.: Requête NATURALINNERJOIN.............................................................................................. 339 Exemple 22.: Requête NATURALLEFTOUTERJOIN ................................................................................... 344 Exemple 23.: Requête ADDCOLUMNS ......................................................................................................... 350 Exemple 24.: Requête ROLLUP ...................................................................................................................... 352 Exemple 25.: Requête ROLLUPGROUP ........................................................................................................ 353 Exemple 26.: Requête ISSUBTOTAL ............................................................................................................. 355 Exemple 27.: Requête ROLLUPADDISSUBTOTAL ..................................................................................... 357

Data Definition (DAX) ......................................................................................................................... 358

Exercice 28.: Commande CALENDAR ........................................................................................................... 359

ti

Solution ......................................................................................................................................................................... 360

Exercice 29.: Commande CALENDARAUTO ................................................................................................ 363 Solution ......................................................................................................................................................................... 364

Exercice 30.: Commande SUMMARIZE ........................................................................................................ 366 Solution ......................................................................................................................................................................... 367

Exercice 31.: Commande ADDCOLUMNS .................................................................................................... 368

an

Solution ......................................................................................................................................................................... 369

Exercice 32.: Commande UNION ................................................................................................................... 370 Solution ......................................................................................................................................................................... 371

Exercice 33.: Commande UNION et ROW ..................................................................................................... 372 Solution ......................................................................................................................................................................... 373

Exercice 34.: Commande UNION et GENERATESERIES ............................................................................ 374 Solution ......................................................................................................................................................................... 376

Exercice 35.: Commande DATATABLE ........................................................................................................ 377 Solution ......................................................................................................................................................................... 378

ch

Exercice 36.: Commande INTERSECTION .................................................................................................... 380 Solution ......................................................................................................................................................................... 381

Exemple 37.: Commande CROSSJOIN ........................................................................................................... 382 Exemple 38.: Commande GENERATESERIES avec génération de de séquences ou nombres aléatoires ..... 386

Data Analysis (DAX) ........................................................................................................................... 389 Compatibilités fonctions DAX ......................................................................................................................... 393 Raccourcis claviers DAX ................................................................................................................................. 394 Commentaires DAX ......................................................................................................................................... 395 Icônes DAX ..................................................................................................................................................... 396 Organiser les fonctions.................................................................................................................................................. 397

É

"Table" de mesures ........................................................................................................................................................................ 398 Dossiers et sous-dossiers de mesures ............................................................................................................................................. 400

Opérateurs DAX .............................................................................................................................................. 403 DAX Catégorie Fonctions d'Informations........................................................................................................ 404 Exercice 1.: Fonction USERNAME.............................................................................................................................. 404 Exercice 2.: Fonction USERPRINCIPALNAME ......................................................................................................... 405 Exercice 3.: Fonction USERCULTURE ....................................................................................................................... 406

DAX Variables................................................................................................................................................. 407 DAX Catégorie Fonctions Logiques ................................................................................................................ 408 Exercice 4.: Fonction IF (+VALUE)............................................................................................................................. 408

Microsoft Power BI / Microsoft Excel BI

5/950

Vincent ISOZ

Solution ......................................................................................................................................................................................... 409

Exercice 5.: Fonction IF, OR (+VALUE) ..................................................................................................................... 410 Solution ......................................................................................................................................................................................... 411

Exercice 6.: Fonction AND ........................................................................................................................................... 412 Exercice 7.: Fonction SWITCH (pour KPI) .................................................................................................................. 413 Solution ......................................................................................................................................................................................... 414

DAX Catégorie de Filtres ................................................................................................................................ 415 Exercice 8.: Fonction RELATED ................................................................................................................................. 415 Solution ......................................................................................................................................................................................... 417

llo n

Exercice 9.: Fonction CALCULATE ............................................................................................................................ 419 Solution ......................................................................................................................................................................................... 420

Exercice 10.: Fonctions FILTER et AND dans CALCULATE .................................................................................... 421 Solution ......................................................................................................................................................................................... 422

Exercice 11.: Fonctions FILTER et IN dans CALCULATE ......................................................................................... 423 Solution ......................................................................................................................................................................................... 424

Exercice 12.: Fonction FILTER avec FOUND ou LEFT .............................................................................................. 428 Solutions ........................................................................................................................................................................................ 429

Exercice 13.: Fonction FILTER avec EVALUATE et ORDER BY ............................................................................. 430 Exercice 14.: Fonction ALL.......................................................................................................................................... 431 Solution ......................................................................................................................................................................................... 432

Exercice 15.: Fonction ALL et REMOVEFILTER avec slicer (segment) ignoré et filtre statique supplémentaire ...... 434 Solution ......................................................................................................................................................................................... 435

Exercice 16.: Fonction ALLSELECTED ...................................................................................................................... 436 Exercice 17.: Fonction ALLSELECTED avec calcul ................................................................................................... 438 Solution ......................................................................................................................................................................................... 439

ti

Exercice 18.: Fonction ALLSELECTED avec colonnes multiples ............................................................................... 440 Solution ......................................................................................................................................................................................... 441

Exercice 19.: Fonction ALLSELECTED et EXCEPT( )............................................................................................... 443 Solution ......................................................................................................................................................................................... 444

Exercice 20.: Fonction ALLSELECTED et ISONORAFTER pour les Running Total ................................................ 446 Exercice 21.: Fonction DISTINCT ............................................................................................................................... 451

an

Solution ......................................................................................................................................................................................... 452

Exercice 22.: Fonction ALLEXCEPT ........................................................................................................................... 453 Solution ......................................................................................................................................................................................... 454

Exercice 23.: Fonction HASONEVALUE avec application pour formatage ................................................................ 455 Solution ......................................................................................................................................................................................... 456

Exercice 24.: Fonction HASONEVALUE vs ISFILTERED vs HASONEFILTER ..................................................... 459 Solution ......................................................................................................................................................................................... 463

Exemple 25.: Requête ALLNOBLANKROW .............................................................................................................. 466 Solution ......................................................................................................................................................................................... 468

Exercice 26.: Fonction USERELATIONSHIP.............................................................................................................. 469 Solution ......................................................................................................................................................................................... 472

ch

Exercice 27.: Fonction SELECTEDVALUE ................................................................................................................ 473 Solution ......................................................................................................................................................................................... 474

Exercice 28.: Fonction INTERSECT avec SUM et CALCULATE .............................................................................. 476 Solution ......................................................................................................................................................................................... 479

Exercice 29.: Fonction TREATAS avec SUM et CALCULATE ................................................................................. 480 Solution ......................................................................................................................................................................................... 483

Exercice 30.: Fonction ISINSCOPE ............................................................................................................................. 485 Solution ......................................................................................................................................................................................... 486

DAX Catégorie Fonctions Statistiques ............................................................................................................ 489 Exercice 31.: Fonction SUM ......................................................................................................................................... 489

É

Solution ......................................................................................................................................................................................... 490

Exercice 32.: Fonction SUMX (+RELATED) ............................................................................................................. 493 Solution ......................................................................................................................................................................................... 495

Exercice 33.: Fonction SUMX (+FILTER +AND) ...................................................................................................... 497 Solution ......................................................................................................................................................................................... 498

Exercice 34.: Interlude sur les SUM et SUMX ............................................................................................................. 503 Exercice 35.: Fonction AVERAGE .............................................................................................................................. 506 Premier cas .................................................................................................................................................................................... 507 Solution ......................................................................................................................................................................................... 508 Deuxième cas................................................................................................................................................................................. 510 Solution ......................................................................................................................................................................................... 511 Troisième cas ................................................................................................................................................................................. 512

Microsoft Power BI / Microsoft Excel BI

6/950

Vincent ISOZ

Solution ......................................................................................................................................................................................... 513

Exercice 36.: Fonction AVERAGEX ............................................................................................................................ 514 Solution ......................................................................................................................................................................................... 515

Exercice 37.: Moyenne Résumée, vs Moyenne Globale, vs Moyenne semi-locale....................................................... 517 Solution ......................................................................................................................................................................................... 518

Exercice 38.: Fonction MIN.......................................................................................................................................... 520 Solution ......................................................................................................................................................................................... 521

Exercice 39.: Fonction MINX ....................................................................................................................................... 523 Solution ......................................................................................................................................................................................... 524

Exercice 40.: Fonction MAX ........................................................................................................................................ 525

llo n

Solutions ........................................................................................................................................................................................ 526

Exercice 41.: Fonction MAXX ..................................................................................................................................... 527 Solutions ........................................................................................................................................................................................ 528

Exercice 42.: Calcul de l'étendue pour utilisation conjointe de MAX et MIN .............................................................. 529 Solution ......................................................................................................................................................................................... 530

Exercice 43.: Utilisation d'un slicer d'intervalle de date avec MAX et MIN ................................................................. 531 Solution ......................................................................................................................................................................................... 532

Exercice 44.: Fonction COUNT .................................................................................................................................... 533 Solution ......................................................................................................................................................................................... 534

Exercice 45.: Fonction COUNT, CALCULATE et USERELATIONSHIP .................................................................. 535 Solution ......................................................................................................................................................................................... 538

Exercice 46.: Fonction COUNTX ................................................................................................................................. 539 Solution ......................................................................................................................................................................................... 540

Exercice 47.: Fonction COUNTA ................................................................................................................................. 541 Solution ......................................................................................................................................................................................... 542

ti

Exercice 48.: Fonction COUNTAX .............................................................................................................................. 543 Solution ......................................................................................................................................................................................... 544

Exercice 49.: Fonction COUNTROWS ........................................................................................................................ 545 Solution ......................................................................................................................................................................................... 546

Exercice 50.: Fonction COUNTBLANK ...................................................................................................................... 547 Solution ......................................................................................................................................................................................... 548

Exercice 51.: Fonction DISTINCTCOUNT .................................................................................................................. 549

an

Solution ......................................................................................................................................................................................... 550

Exercice 52.: Fonction PERCENTILEX.INC ............................................................................................................... 551 Solution ......................................................................................................................................................................................... 552

Exercice 53.: Fonction PERCENTILE.INC .................................................................................................................. 555 Solution ......................................................................................................................................................................................... 556

Exercice 54.: Fonction TOPN simple ........................................................................................................................... 557 Solutions ........................................................................................................................................................................................ 558

Exercice 55.: Fonction TOPN dans calcul .................................................................................................................... 559 Solution ......................................................................................................................................................................................... 560

Exercice 56.: Fonction TOPN dans calcul avec paramètre dynamique ......................................................................... 561

ch

Solution ......................................................................................................................................................................................... 562

Exercice 57.: Fonction TOPN en tant que requête ........................................................................................................ 564 Exercice 58.: Fonction RANKX simple avec ALL( ) ................................................................................................... 565 Solution ......................................................................................................................................................................................... 566

Exercice 59.: Fonction RANK.EQ ................................................................................................................................ 570 Solution ......................................................................................................................................................................................... 571

DAX Catégorie Fonctions de Temps ............................................................................................................... 574 Exercice 60.: Fonctions TODAY et NOW .................................................................................................................... 574 Solutions ........................................................................................................................................................................................ 575

É

Exercice 61.: Fonction WEEKDAY ............................................................................................................................. 576 Exercice 62.: Fonction YEAR....................................................................................................................................... 577 Exercice 63.: Fonction MONTH ................................................................................................................................... 578 Exercice 64.: Fonction EOMONTH.............................................................................................................................. 579 Exercice 65.: Fonction DAY ......................................................................................................................................... 580 Exercice 66.: Fonction DATE et SUM et CALCULATE ............................................................................................ 581 Solution ......................................................................................................................................................................................... 584

Exercice 67.: Fonctions YEARFRAC et INT et TODAY ............................................................................................ 585 Solution ......................................................................................................................................................................................... 586

Exercice 68.: Fonction DATEDIFF .............................................................................................................................. 587 Solution ......................................................................................................................................................................................... 588

Exercice 69.: Fonctions PREVIOUSDAY et NEXTDAY ........................................................................................... 589

Microsoft Power BI / Microsoft Excel BI

7/950

Vincent ISOZ

Solution ......................................................................................................................................................................................... 591

Exercice 70.: Fonctions PREVIOUSMONTH et NEXTMONTH ............................................................................... 593 Solution ......................................................................................................................................................................................... 595

Exercice 71.: Fonctions PREVIOUSQUARTER et NEXTQUARTER ....................................................................... 597 Solution ......................................................................................................................................................................................... 599

Exercice 72.: Fonctions PREVIOUSYEAR et NEXTYEAR ....................................................................................... 601 Solution ......................................................................................................................................................................................... 603

Exercice 73.: Fonction SAMEPERIODLASTYEAR ................................................................................................... 607 Solution ......................................................................................................................................................................................... 609

Exercice 74.: Fonction PARALLELPERIOD ............................................................................................................... 610

llo n

Solution ......................................................................................................................................................................................... 612

Exercice 75.: Fonction DATEADD .............................................................................................................................. 616 Solution ......................................................................................................................................................................................... 618

Exercice 76.: PARALLELPERIOD vs DATEADD vs SAMEPERIODLASTYEAR .................................................. 620 Solution ......................................................................................................................................................................................... 622

Exercice 77.: Fonctions DATESMTD et DATESYTD................................................................................................ 626 Solution ......................................................................................................................................................................................... 628

Exercice 78.: Fonctions FIRSTDATE et LASTDATE ................................................................................................ 631 Premier cas .................................................................................................................................................................................... 632 Solution ......................................................................................................................................................................................... 634 Deuxième cas................................................................................................................................................................................. 637 Solution ......................................................................................................................................................................................... 638

Exercice 79.: Fonction ENDOFMONTH et CLOSINGBALANCEMONTH ............................................................... 639 Solution ......................................................................................................................................................................................... 641

Exercice 80.: Fonction TOTALX to Date sans date fiscale (TOTALMTD, TOTALQTD, TOTALYTD) ................... 643 Solution ......................................................................................................................................................................................... 644

ti

Exercice 81.: Fonction TOTALX to Date avec date fiscale (TOTALMTD, TOTALQTD, TOTALYTD) ................... 647 Solution ......................................................................................................................................................................................... 649

Exercice 82.: Fonction DATESBETWEEN .................................................................................................................. 650 Solution ......................................................................................................................................................................................... 651

Exercice 83.: STARTOFX et ENDOFX ....................................................................................................................... 652 Solution ......................................................................................................................................................................................... 653

an

Exercice 84.: Fonction DATESINPERIOD et ENDOFMONTH .................................................................................. 654 Solution ......................................................................................................................................................................................... 656

Exercice 85.: Fonctions DATESBETWEEN( ) vs DATESINPERIOD( ) .................................................................... 658 Solution ......................................................................................................................................................................................... 659

Exercice 86.: Fonction PREVIOUSX et NEXTX ......................................................................................................... 660 Solution ......................................................................................................................................................................................... 661

Exercice 87.: Fonction FIRSTNONBLANK ................................................................................................................ 663 Solution ......................................................................................................................................................................................... 665

Exercice 88.: Fonction EARLIER ................................................................................................................................. 667 Solution ......................................................................................................................................................................................... 668

ch

Exercice 89.: Fonction EARLIER pour "SmartIndex" .................................................................................................. 671 Solution (méthode "ancienne" avec EARLIER( )) ......................................................................................................................... 672 Solution (méthode "moderne" sans EARLIER( )).......................................................................................................................... 673

Exercice 90.: Mois précédent filtré ............................................................................................................................... 674 Solution ......................................................................................................................................................................................... 675

DAX Catégorie Fonctions d'Information et Parent/Enfant .............................................................................. 677 Exercice 91.: Fonction LOOKUPVALUE pour relation circulaire ............................................................................... 677 Solution en Power Query ............................................................................................................................................................... 678

Exercice 92.: Fonction LOOKUPVALUE pour sélection de scénarios ........................................................................ 682 Solution ......................................................................................................................................................................................... 683

Exercice 93.: Fonction PATH ....................................................................................................................................... 684

É

Solution ......................................................................................................................................................................................... 685

Exercice 94.: Fonction LOOKUPVALUE et PATHITEM ........................................................................................... 686 Solution ......................................................................................................................................................................................... 688

DAX Catégorie Fonctions de Texte ................................................................................................................. 690 Exercice 95.: Fonction FORMAT (+WEEKDAY, ADDCOLUMNS et YEAR et DAY( ) ) ........................................ 690 Solution ......................................................................................................................................................................................... 691 Tables des valeurs par défauts de la fonction FORMAT ................................................................................................................ 692

Exercice 96.: Fonction VALUE .................................................................................................................................... 693 Exercice 97.: Fonction UNICHAR (+VAR) ................................................................................................................. 694 Solution ......................................................................................................................................................................................... 695

Exercice 98.: Fonction FIND( ) et UPPER( ) ................................................................................................................ 697

Microsoft Power BI / Microsoft Excel BI

8/950

Vincent ISOZ

Solution ......................................................................................................................................................................................... 698

Exercice 99.: Fonction VALUES avec SEARCH( ) et FIRSTNONBLANK( ) ........................................................... 699 Solution ......................................................................................................................................................................................... 700

Exercice 100.: Fonction CONCATENATEX( ) ............................................................................................................ 701 Solution ......................................................................................................................................................................................... 702

DAX Catégorie Finance ................................................................................................................................... 703 Exercice 101.: Fonction XIRR (annuel) ........................................................................................................................ 703 Solution ......................................................................................................................................................................................... 704

Exercice 102.: Fonction XNPV..................................................................................................................................... 706

llo n

Solution ......................................................................................................................................................................................... 707

DAX Patterns ................................................................................................................................................... 708 Exercice 103.: Quick Measures (mesures rapides) ........................................................................................................ 709

Catégorie Texts .............................................................................................................................................................................. 713 Catégorie Mathematical operations................................................................................................................................................ 723 Catégorie Totals ............................................................................................................................................................................. 738 Catégorie Time Intelligence ........................................................................................................................................................... 743 Catégorie Aggregate per category .................................................................................................................................................. 781 Catégorie Filters............................................................................................................................................................................. 794

Exercice 104.: Capabilité 6 Sigma ................................................................................................................................ 811 Exercice 105.: Moyenne Mobile ................................................................................................................................... 813 Solution ......................................................................................................................................................................................... 814

Exercice 106.: Valeur modale (cas unimodal) .............................................................................................................. 819 Solution ......................................................................................................................................................................................... 820

Exercice 107.: Médiane................................................................................................................................................. 823 Solution ......................................................................................................................................................................................... 824

ti

Exercice 108.: Moyenne pondérée ................................................................................................................................ 825 Solution ......................................................................................................................................................................................... 826

18. Data Optimization............................................................................................................. 827

an

Désactiver le Auto Date/Time .............................................................................................................. 828 Performance Analyzer .......................................................................................................................... 830 DAX Studio .......................................................................................................................................... 833 Utiliser le Query Folding ...................................................................................................................... 836 Identifier les colonnes gourmandes en mémoire (DAX Studio) ........................................................... 837 Data Preview de Power Query.............................................................................................................. 839

19. Data Viz.............................................................................................................................. 841 Thèmes.................................................................................................................................................. 842 Rappels importants des pièges dans les fichiers d'exercices................................................................. 843 Data Viz Normal (Desktop) screen....................................................................................................... 844

ch

Boutons ............................................................................................................................................................ 844 Créer des boutons de navigation ................................................................................................................................... 844 Créer des boutons pour ouvrir des liens ........................................................................................................................ 844 Créer des boutons pour effacer tous les filtres .............................................................................................................. 844

Q&A................................................................................................................................................................. 844 Slicers (segments) ............................................................................................................................................ 847

É

Slicers de dates relatifs.................................................................................................................................................. 847 Trier un slicer (segment) par ordre chronologique des mois ......................................................................................... 848 Trier un slicer (segment) par ordre spécifique .............................................................................................................. 849 Slicer Between de dates, filtré par des dates ................................................................................................................. 850 Masquer/Réafficher un slicer ........................................................................................................................................ 850

Symboles Unicodes .......................................................................................................................................... 853 Créer des groupes ............................................................................................................................................. 853 Formatage conditionnel des barres en fonction d'une valeur cible ................................................................... 854 Titres dynamiques (avril 2019) ........................................................................................................................ 855 Visuel Word Cloud .......................................................................................................................................... 856 Visuel Microsoft Visio ..................................................................................................................................... 857 Visuel Synoptic Panel by OKViz ..................................................................................................................... 857

Data Viz Mobile (phone) screen ........................................................................................................... 859

Microsoft Power BI / Microsoft Excel BI

9/950

Vincent ISOZ

20. Data Sharing (Power BI Services) ................................................................................... 860 Custom Branding .................................................................................................................................. 862 Dashboard ............................................................................................................................................. 864 Workspaces App (espaces de travail) ................................................................................................... 865 Partager un Workspace App............................................................................................................................. 865 Publier un Workspace App .............................................................................................................................. 865 Workspaces orphelins ...................................................................................................................................... 873

llo n

RLS (Row Level Security) ................................................................................................................... 877 Partage .................................................................................................................................................. 888

Embed Code ..................................................................................................................................................... 888 Intégration dans Microsoft Word ..................................................................................................................... 893 Intégration dans Microsoft Excel ..................................................................................................................... 895 Intégration dans Microsoft PowerPoint............................................................................................................ 896 Intégration dans Microsoft Teams.................................................................................................................... 899 Intégration dans Microsoft OneNote App ........................................................................................................ 901

Import de fichiers Microsoft Excel ....................................................................................................... 905 Liens avec filtres ................................................................................................................................... 916 Alertes................................................................................................................................................... 917 Power BI Embedded ............................................................................................................................. 918

ti

Créer un Tenant................................................................................................................................................ 921 Obtenir le Client ID (ie Application ID) et Client Secret ................................................................................. 921 Obtenir l'ID du rapport ..................................................................................................................................... 926 Obtenir le Tenant ID ........................................................................................................................................ 927 Intégration ........................................................................................................................................................ 930 C# .Net .......................................................................................................................................................................... 930 Javascript ...................................................................................................................................................................... 931

an

Administration ...................................................................................................................................... 933 Monitorer les coûts........................................................................................................................................... 936

21. Report Builder................................................................................................................... 940 Data Munging ....................................................................................................................................... 941 Microsoft SQL Server Express ........................................................................................................................ 941 Microsoft Access ............................................................................................................................................. 942

Intelligence Artificielle et Analyse avancée avec R et Python....................................... 945 FAQ (Frequently Asked Questions) ................................................................................ 946 Assessment (questions de révision) .................................................................................. 947 Listes des tableaux et figures ........................................................................................... 948 Index ................................................................................................................................... 949

É

ch

22. 23. 24. 25. 26.

Microsoft Power BI / Microsoft Excel BI

10/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

11/950

Vincent ISOZ

2. À propos du rédacteur

llo n

Nom Prénom: ISOZ Vincent Domicilié à ce jour à Lausanne (Suisse)

Formation: Ingénieur Physicien HES Équivalence Internationale: Bachelor of Science Année de naissance: 1978

Je suis consultant en mathématiques appliquées dans le tutorat d'analystes quantitatifs (niveau Bac+5 à Bac+7) et auteur de plusieurs livres électroniques dans les domaines suivants: maîtrise statistique des processus/procédés (méthodes paramétriques et non paramétriques) avec Minitab

•

modélisation prévisionnelle/décisionnelle avancée (arbres de décisions, chaînes de Markov)

•

recherche opérationnelle (simplexe, algorithmes génétiques, algorithme GRG)

•

data mining / machine learning (réseaux de neurones, ACP, AFC, régressions, scoring, clustering, etc.) avec R, Python ou MATLAB

•

modélisation du risque en gestion de projets et finance d'entreprise (monte-carlo, etc.)

•

gestion de projets (modèles et best practices théoriques EFQM+Six Sigma, Microsoft Project)

ch

an

ti

•

ISO 9001:2008, 5807:1985, 10015:1999, 31000+31010:2009, 8258:1991, 10017:2003, etc.

•

Adobe Photoshop et Illustrator

•

12 applications de la suite Microsoft Office System (Project, Visio, SharePoint, Access, etc.)

É

•

À ce jour, j'ai à mon actif des interventions dans plus de ~200 entreprises dont 10 du Fortune 500 selon listing 2009 et 3 universités et écoles d'ingénieurs suisses (pour des cours de modélisation de bases de données et simulations stochastiques du risque). J'ai également fait des formations de plusieurs dirigeants de multinationales en one to one. Accessoirement j'interviens pour des formations sur des logiciels comme Microsoft Project, @Risk, MS Visio, Microsoft Access, MS SharePoint (architecture et gouvernance) et une Microsoft Power BI / Microsoft Excel BI

12/950

Vincent ISOZ

É

ch

an

ti

llo n

vingtaine d'autres dont je délègue l'organisation à des entreprises spécialisées dans la formation continue en bureautique (niveau licence et en-dessous).

Microsoft Power BI / Microsoft Excel BI

13/950

Vincent ISOZ

3. Avertissement

llo n

Le contenu du présent support est élaboré par un processus de développement par lequel des experts DAX sont parvenus à un consensus. Ce processus qui rassemble des participants bénévoles recherche également les points de vue de personnes intéressées par le sujet de cet ouvrage. En tant que responsable du présent support, j'assure l'administration du processus et je fixe les règles qui permettent de promouvoir l'équité dans l'approche d'un consensus. Je me charge également de rédiger les textes, parfois de les tester/évaluer ou de vérifier indépendamment l'exactitude/solidité ou l'exhaustivité des informations présentées.

ti

Je décline toute responsabilité en cas de dommages corporels, matériels ou autres de quelque nature que ce soit, particuliers, indirects, accessoires ou compensatoires, résultant de la publication, de l'application ou de la confiance accordée au contenu du présent support. Je n'émets aucune garantie expresse ou implicite quant à l'exactitude ou à l'exhaustivité de toute information publiée dans le présent support, et ne garantit aucunement que les informations contenues dans cet ouvrage satisfassent un quelconque objectif ou besoin spécifique du lecteur. Je ne garantis pas non plus les performances de produits ou de services d'un fabricant ou d'un vendeur par la seule vertu du contenu du présent support.

an

En publiant des textes, il n'est pas dans l'intention principale du présent support de fournir des services de spécialistes ou autres au nom de toute personne physique ou morale ni pour mon compte, ni d'effectuer toute tâche devant être accomplie par toute personne physique ou morale au bénéfice d'un tiers. Toute personne utilisant le présent support devrait s'appuyer sur son propre jugement indépendant ou, lorsque cela s'avère approprié, faire appel aux conseils d'un spécialiste compétent afin de déterminer comment exercer une prudence raisonnable en toute circonstance. Les informations et les normes concernant le sujet couvert par le présent support peuvent être disponibles auprès d'autres sources que le lecteur pourra souhaiter consulter en quête de points de vue ou d'informations supplémentaires qui ne seraient pas couverts par le contenu du présent site Internet.

É

ch

Je ne dispose (malheureusement...) d'aucun pouvoir dans le but de faire respecter la conformité au contenu du présent ouvrage, et je ne m'engage nullement à surveiller ni à faire respecter une telle conformité. Je n'exerce (à ce jour...) aucune activité de certification, de test ni d'inspection de produits, de conceptions ou d'installations à fins de santé ou de sécurité des personnes et des biens. Toute certification ou autre déclaration de conformité en matière d'informations ayant trait à la santé ou à la sécurité des personnes et des biens, mentionnée dans le présent support, ne peut aucunement être attribuée au contenu du présent support et demeure sous l'unique responsabilité de l'organisme de certification ou du déclarant concerné.

Microsoft Power BI / Microsoft Excel BI

14/950

Vincent ISOZ

4. Votre avis nous intéresse! En tant que lecteur de ce document, vous êtes le critique et le commentateur le plus important. Votre opinion compte et il est très intéressant de savoir ce qui est bien, ce qui peut être mieux et les sujets que vous souhaiteriez voir être traités.

llo n

Vous pouvez m'envoyer un e-mail pour partager ce que vous avez aimé ou détesté dans le présent document afin d'en assurer une amélioration continue.

Notez que malheureusement, je ne peux pas répondre gratuitement à des questions techniques d'ingénierie ou de problématique d'entreprise par e-mail pour des raisons professionnelles évidentes.

É

ch

an

ti

E-mail: [email protected]

Microsoft Power BI / Microsoft Excel BI

15/950

Vincent ISOZ

5. Préambule Attention! Ce document est un support de notes et d'exercices plus qu'un livre sur Power BI (il en existe déjà de nombreux sur le sujet de plusieurs milliers de pages donc cela ne vaut pas la peine de recréer la roue).

llo n

Le seul intérêt de ce support est de compiler au propre les questions qui m'ont été le plus souvent demandées par mes clients et participants à ce jour et particulièrement sur l'aspect DAX de Microsoft Power BI.

L'ordre dans lesquels les sujets sont présentés peut être perturbant mais gardez à l'esprit encore une fois que ce document évolue au fur et à mesure de mes interventions! Il n'y pas été écrit dans l'optique de faire un livre publié ou publiable sur Microsoft Power BI!!!

É

ch

an

ti

Quitte à me répéter… pour ceux qui veulent apprendre la Data Science ou Data Analysis avec R ou Python dans Microsoft Power BI, référez-vous aux supports de cours y relatifs (2300 pages sur R et environ 700 pages sur Python).

Microsoft Power BI / Microsoft Excel BI

16/950

Vincent ISOZ

6. Copyright

llo n

Ce document est soumis à la réglementation européenne sur le droit d'auteurs et droits voisins. Même si vous pouvez l'échanger entre collègues et amis sans aucun danger, nous interdisons sa publication et partage sur Internet (Scribd, Archive.org, Libgen, LinkedIn, Facebook, Twitter, PirateBay et autre).

É

ch

an

ti

Nous vous remercions pour votre compréhension.

Microsoft Power BI / Microsoft Excel BI

17/950

Vincent ISOZ

7. Médiagraphie (liens) Voici quelques liens internet très utiles si vous êtes utilisateur Power BI: https://powerbi.microsoft.com/en-us/blog/ https://ideas.powerbi.com https://www.microsoft.com/en-us/microsoft-365/roadmap http://community.powerbi.com/t5/Forums/ct-p/Forums https://www.daxpatterns.com/ https://pragmaticworks.com/training/on-demand-training https://www.linkedin.com/learning/ https://ideas.powerbi.com/forums/265200-power-bi-ideas https://curbal.com/donwload-center https://docs.microsoft.com/en-us/

llo n

• • • • • • • • • •

Quelques add-ins:

ti

https://www.sqlbi.com/tools/vertipaq-analyzer/ https://www.sqlbi.com/tools/dax-studio/ http://radacad.com/power-bi-helper https://powerbi.tips/tools/report-theme-generator-v3/ https://www.powerbitiles.com/?app=PBIRobots https://binokula.com/features/ https://charticulator.com/ https://synoptic.design/

an

• • • • • • • •

Toutes les versions précédentes de Power BI:

https://zenatti.net/2017/07/04/power-bi-previous-versions/

ch

•

Voici un lien vers un PDF utile détaillant de nombreuses fonctions DAX: •

https://download.microsoft.com/download/0/F/B/0FBFAA46-2BFD-478F-8E567BF3C672DF9D/Data%20Analysis%20Expressions%20-%20DAX%20%20Reference.pdf

É

Ou un autre lien utile relativement à DAX: •

https://dax.guide/

Quelques liens vers de visuels pros pour Power BI et exemple de tableaux de bords: • •

https://zoomcharts.com https://powerbi.microsoft.com/en-us/partner-showcase/

Microsoft Power BI / Microsoft Excel BI

18/950

Vincent ISOZ

Et les chaînes YouTube les plus importantes (toutes en anglais) et dans l'ordre d'importance: • • •

https://www.youtube.com/channel/UCy--PYvwBwAeuYaR8JLmrfg https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw https://www.youtube.com/channel/UCy2rBgj4M1tzK-urTZ28zcA

• •

llo n

Le lien vers le code source (GitHub) de tous les visuels Power BI afin d'apprendre à en créer des personnalisés: https://github.com/Microsoft/PowerBI-visuals https://microsoft.github.io/PowerBI-visuals/samples/

É

ch

an

ti

Voici les livres que je recommande à ce jour (dans l'ordre de lecture en haut à gauche d'en bas à droite):

Et pour les vidéos de votre serviteur (disponible avec votre abonnement LinkedIn ou via le site web Video2Brain.com):

Microsoft Power BI / Microsoft Excel BI

19/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

20/950

Vincent ISOZ

8. Introduction Je tiens d'abord à reproduire ici les illustrations utilisées dans la présentation PowerPoint que j'utilise pour présenter l'écosystème Power BI et R à mes clients. Cependant les illustrations ne seront que peu commentées!

ch

an

ti

llo n

Voici d'abord le dernier quadrant magique de Gartner relativement à la BI:

É

Rappelons, car cela aide à comprendre le fonctionnement de la Data Science, le flux suivant de travail:

Microsoft Power BI / Microsoft Excel BI

21/950

ti

llo n

Vincent ISOZ

ch

an

Ce qui correspond explicitement dans l'écosystème Power BI à:

Power View

Power Map

Power BI/R

É

Power Pivot/DAX/MDX Power Query/M

Microsoft Power BI / Microsoft Excel BI

22/950

Vincent ISOZ

an

ti

llo n

Il peut être intéressant de bien différencier la Data Science avec la Business Analysis comme l'illustre le diagramme suivant:

É

ch

Ou avec une autre approche pédagogique:

Microsoft Power BI / Microsoft Excel BI

23/950

Vincent ISOZ

an

ti

llo n

Ou encore:

É

ch

Et les différents rôles qui font consensus dans le domaine de la Data Science:

Microsoft Power BI / Microsoft Excel BI

24/950

Vincent ISOZ

ti

llo n

Pour résumer un peu, Power BI c'est:

É

ch

an

Pour ceux qui n'ont jamais vu à quoi ressemble Microsoft Power Query dans Excel ou dans Power BI, voici une capture d'écran:

Microsoft Power BI / Microsoft Excel BI

25/950

an

ti

llo n

Vincent ISOZ

É

ch

Figure 1Vue d'ensemble de Power Query

Microsoft Power BI / Microsoft Excel BI

26/950

ti

llo n

Vincent ISOZ

É

ch

an

Figure 2 Types des jointures typiques en SQL et Power Query

Microsoft Power BI / Microsoft Excel BI

27/950

an

ti

llo n

Vincent ISOZ

É

ch

Figure 3 Vue d'ensemble de Power Pivot

Microsoft Power BI / Microsoft Excel BI

28/950

an

ti

llo n

Vincent ISOZ

É

ch

Figure 4Vue d'ensemble de Power View

Microsoft Power BI / Microsoft Excel BI

29/950

ti

llo n

Vincent ISOZ

É

ch

an

Figure 5Vue d'ensemble de Power Map

Microsoft Power BI / Microsoft Excel BI

30/950

an

ti

llo n

Vincent ISOZ

É

ch

Figure 6Vue d'ensemble de Power BI

Microsoft Power BI / Microsoft Excel BI

31/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

32/950

an

ti

llo n

Vincent ISOZ

É

ch

Figure 7Vue d'ensemble de R Shiny

Microsoft Power BI / Microsoft Excel BI

33/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

34/950

Vincent ISOZ

Modélisation de bases de données (schémas en étoile ou flocon) J'ai déjà écrit un chapitre sur la modélisation de bases de données dans mon livre sur Microsoft Access.

llo n

Le lecteur pourra le trouver ici:

http://www.sciences.ch/dwnldbl/divers/MSAccess.pdf

É

ch

an

ti

pages 21 à 47.

Microsoft Power BI / Microsoft Excel BI

35/950

Vincent ISOZ

9. Nouveautés La liste des nouveautés mensuelles de Power BI depuis Février 2015est résumée ici en français: https://docs.microsoft.com/fr-fr/power-bi/desktop-latest-update-archive

•

• •

Création de rapports o Regroupement des visuels o Migration vers le volet de filtre o Sélecteur de style d’icône pour la mise en forme conditionnelle des icônes o Avertissements concernant la mise en forme conditionnelle Analytique o Améliorations des visuels des influenceurs clés, disponibilité générale Éléments visuels o Nouveaux visuels personnalisés et mises à jour Connectivité aux données o Prise en charge des conteneurs SAP HANA HDI o Modifier les variables SAP dans le service Power BI (préversion) o PostgreSQL DirectQuery o Connecteur MarkLogic maintenant en disponibilité générale o Nouvelle catégorie Power Platform dans Obtenir des données Applications modèles o Pages Facebook - Analytique de base

•

Juillet 2019

Création de rapports o Jeux d’icônes pour les tableaux et les matrices o Prise en charge des pourcentages pour la mise en forme conditionnelle par des règles o Nouveau volet Filtrer à présent en disponibilité générale o Prise en charge des couleurs de données lors de l’utilisation d’un axe de lecture sur des graphiques à nuages de points o Améliorations des performances lors de l’utilisation des dates relatives et des sélecteurs déroulants Analytique o Totaux pour le visuel Influenceurs clés (préversion) o Améliorations des agrégations Éléments visuels o Le visuel PowerApps est désormais certifié o Trois nouveaux visuels personnalisés et des mises à jour Connectivité aux données o Connecteur Azure Data Lake Storage Gen2 (bêta)

ch

•

an

ti

•

llo n

Août 2019

É

• • •

Microsoft Power BI / Microsoft Excel BI

36/950

Vincent ISOZ

• •

o Connecteur Dynamics 365 Customer Insights Préparation des données Nouvelle transformation : Fractionner la colonne par position

Juin 2019

• •

llo n

ch

•

ti

•

Création de rapports o Mise à jour de la conception du volet o Filtres de niveau visuel pour les sélecteurs o Tri pour le volet Analyseur de performances o Mises à jour de la mise en forme conditionnelle pour les options de mise en forme o Info-bulles pour l’en-tête des visuels o Personnalisation de l’étiquette du total des tableaux et des matrices o Prise en charge de la synchronisation des sélecteurs pour le sélecteur de hiérarchie o Tailles de police cohérentes entre les visuels Analytique o Mises à jour des visuels des influenceurs clés (préversion) o Influenceurs clés avec Live Connect pour les jeux de données Power BI o Accessibilité des influenceurs clés o Prise en charge de la sécurité au niveau des lignes cloud o Accessibilité de la boîte de dialogue Gérer les agrégations Modélisation o Nouvel opérateur de comparaison DAX = = Éléments visuels o Volet de visualisation personnalisé (préversion) o Trois nouveaux visuels personnalisés et des mises à jour Connectivité aux données o Se connecter à des jeux de données partagés et certifiés o Connecteur Common Data Service désormais en disponibilité générale o Connecteur Azure Data Explorer désormais en disponibilité générale o Améliorations apportées au connecteur Cosmos DB o Connecteur de données Entersoft Préparation des données o Améliorations apportées à M Intellisense

an

•

•

Mai 2019

Création de rapports o Volet Analyseur de performances o Améliorations des volets de filtre (préversion) o Navigation avec le clavier dans les tableaux et les matrices o Contrôle de la position des étiquettes de données sur les lignes o Contrôle de la taille du texte des indicateurs visuels des KPI Analytique o Améliorations des visuels des influenceurs clés (préversion)

É

•

•

Microsoft Power BI / Microsoft Excel BI

37/950

Vincent ISOZ

• •

•

llo n

•

o Prise en charge du compartimentage o Options de mise en forme o Prise en charge mobile Modélisation o Désactiver les tableaux de dates automatiques pour les nouveaux rapports o Mise à jour de la fonction DAX ALLSELECTED Éléments visuels o Mises à jour d’ArcGIS Maps for Power BI o Six nouveaux visuels personnalisés et des mises à jour Connectivité aux données o Connecteur Essbase - Prise en charge des requêtes natives (MDX) o Connecteur Intune Data Warehouse o Connecteur Tenforce o Connecteur Roamler Autre o Annulation automatique des requêtes pour Power BI Desktop

ch

•

Création de rapports o Améliorations du volet des filtres o Mise en forme conditionnelle pour les titres des visuels o Mise en forme conditionnelle pour les actions d’URL web pour les boutons, les formes et les images Analytique o Exploration entre les rapports o Le visuel Influenceurs clés prend désormais en charge l’analyse en continu pour les cibles numériques o Prise en charge de Python - pas en disponibilité générale o Mise en correspondance partielle de synonymes de termes dans Questions et réponses Modélisation o Nouvelle fonction DAX - ALLCROSSFILTERED Visuels personnalisés o Un nouveau visuel personnalisé Connectivité aux données o Connecteur de dataflow Power BI maintenant en disponibilité o Le connecteur Oracle Essbase prend désormais en charge DirectQuery et est en disponibilité générale o Connecteur PDF maintenant en disponibilité générale o Connecteur Web By Example - inférence automatique des tables o Connecteur Intersystems IRIS o Connecteur Indexima o Connecteur Luminis InformationGrid o Connecteur Solver BI360

an

•

ti

Avril 2019

• •

É

•

Microsoft Power BI / Microsoft Excel BI

38/950

Vincent ISOZ

•

llo n

•

o Connecteur Paxata Préparation des données o Améliorations et disponibilité générale du profilage des données o Améliorations des performances et disponibilité générale de la fusion approximative o M Intellisense est pris en charge dans la barre de formule et la boîte de dialogue des colonnes personnalisées, et est en disponibilité générale Autre o Générateur de rapports paginés Power BI

Mars 2019

Création de rapports o Segment à sélection unique o Prise en charge de cartes Bing thermiques o Sélection par étiquettes d’axes o Mise en forme d’info-bulles par défau o Prise en charge d’URL web statiques pour les boutons, formes et images o Améliorations des volets de filtre o Options d’alignement des pages o Sélection des volets de filtre o Mises à jour de la mise en forme de cartes o Interaction visuelle accessible Analytique o Recommandations sous forme de questions et réponses pour améliorer les résultats o Affichage des dates sous forme de hiérarchie maintenant mis à la disposition générale Modélisation o Nouvelle vue de modélisation maintenant en disponibilité générale o Nouvelles fonctions DAX Visuels personnalisés o Paramètre de visuels personnalisés nouvellement certifiés dans le portail d’administration o Deux nouveaux visuels personnalisés Connectivité aux données o Le connecteur PDF prend désormais en charge les tables s’étendant sur plusieurs pages (préversion) o Connecteur industriel pour installation intelligente de l'App Store o Connecteur Azure Cost Management

•

ch

•

an

ti

•

•

É

•

….

Microsoft Power BI / Microsoft Excel BI

39/950

Vincent ISOZ

10. Licences

an

ti

llo n

Nous donnerons plus loin un calculateur que Microsoft à mis en ligne pour dimensionner correctement les licences. Mais d'abord voir un résumé non-exhaustif:

É

ch

Ensuite relativement à R, voici ce qu'il faut savoir:

Microsoft Power BI / Microsoft Excel BI

40/950

Vincent ISOZ

https://docs.microsoft.com/en-us/power-bi/service-r-visuals

É

ch

an

ti

llo n

Ou sous un autre format globalement:

Pour plus de détails sur Microsoft Power Report Server, se rendre à la page suivante: Microsoft Power BI / Microsoft Excel BI

41/950

Vincent ISOZ

https://docs.microsoft.com/en-us/power-bi/report-server/compare-report-server-service Pour plus d'informations: https://powerbi.microsoft.com/fr-fr/pricing/

llo n

https://powerbi.microsoft.com/en-us/calculator/

an

ti

Voici les plans actuel de capacité de Microsoft Power BI:

ch

Pour intégrer Power BI dans des applications personnalisées (Power BI Embedded), six plans de capacité sont disponibles via le modèle de licence Premium.

É

La capacité Power BI Embedded est facturée à l'heure, peut être achetée à l'heure et peut être suspendue, ce qui signifie qu'aucun engagement à long terme pour une capacité spécifique.

Microsoft propose 3 SKU pour la version la version embedded: Microsoft Power BI / Microsoft Excel BI

42/950

an

ti

llo n

Vincent ISOZ

É

ch

Point important:

Microsoft Power BI / Microsoft Excel BI

43/950

ti

É

ch

an

Mais à savoir aussi:

llo n

Vincent ISOZ

Pour plus d'informations: https://docs.microsoft.com/en-us/power-bi/developer/embedded-faq

Microsoft Power BI / Microsoft Excel BI

44/950

Vincent ISOZ

11. Limites Voici les limites les plus problématiques qui me sont remontées actuellement concernant Power BI: Le logiciel n'existe que pour environnement Microsoft Windows (donc pas pour Mac ni pour Linux)

•

Les tables manuelles ne peuvent pas à ce jour avoir plus de 500 lignes (heureusement on peut en faire plusieurs et les fusionner par la suite via Power Query!)

•

Le langage DAX est peu (ou pas du tout!) intuitif

•

Le nombre de graphiques est limité en comparaison à ce que sait faire R (la liste est trop longue pour être énumérée ici)

•

Le nombre de contrôles de formulaire pour les tableaux de bords est limité (par de radio button, ni d'input box, ou de calendar picker)

•

Impossible de copier des feuilles dans d'autres fichiers pbix (idem avec les visualisations)

ti

llo n

•

an

Et il suffit de consulter la liste des propositions d'améliorations des utilisateurs de Power BI pour en voir beaucoup plus:

É

ch

https://ideas.powerbi.com/forums/265200-power-bi-ideas

Microsoft Power BI / Microsoft Excel BI

45/950

Vincent ISOZ

É

ch

an

ti

llo n

12. Meilleures Pratiques (best practices)

Microsoft Power BI / Microsoft Excel BI

46/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

47/950

Vincent ISOZ

13. Moteurs VertiPaq et XVelocity

an

ti

llo n

Ce qui s'exécute quand vous ouvrez PowerBI:

É

ch

On le voit mieux dans le détail des processus:

Voir l'espace de travail de SSAAS lorsqu'un fichier Power BI est ouvert: C:\Users\<user>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces Microsoft Power BI / Microsoft Excel BI

48/950

Vincent ISOZ

Initialement appelé moteur VertiPaq, xVelocity est une base de données en colonnes en mémoire où toutes les données d'un modèle résident en RAM.

Techniques de compression

•Moins d'utilisation de mémoire vive et des coûts réduits •Augmentation des performances avec moins de données à analyser

llo n

Algorithme de compression

•Dictionnaire / Codage en hachage •Encodage de valeur •Codage en longueur

ch

an

ti

Un dictionnaire de valeurs uniques dans une colonne est créé, puis les valeurs de données sont remplacées par les valeurs de l'index du dictionnaire.

É

xVelocity recherche des relations mathématiques entre les valeurs d'une colonne, puis utilise la valeur elle-même, avec ou sans opération mathématique appliquée, pour réduire l'utilisation de la mémoire.

Microsoft Power BI / Microsoft Excel BI

49/950

llo n

Vincent ISOZ

Moins de stockage utilisé pour une valeur inférieure en soustrayant 100000.

ti

Rajoute la valeur lorsque nécessaire.

an

Technique de compression complémentaire qui tente de réduire la taille d'un ensemble de données en évitant les valeurs répétées.

É

ch

Répété 210 fois

Répété 260 fois

Une colonne peut comporter à la fois un codage de longueur d’exécution et un codage de dictionnaire OU de valeur! Microsoft Power BI / Microsoft Excel BI

50/950

Vincent ISOZ

Pour améliorer les performances, les relations entre les tables sont stockées en mémoire sous forme de paires d’ID et de numéros de lignes.

É

ch

an

ti

llo n

ligne 1 ligne 2 ligne 3

Microsoft Power BI / Microsoft Excel BI

51/950

Vincent ISOZ

É

ch

an

ti

llo n

14. Data Munging

Microsoft Power BI / Microsoft Excel BI

52/950

Vincent ISOZ

Data Input Pour la saisie de données dans Power BI via des formulaires, le lecteur et participant à la formation pourra se reporter au visuel suivant:

an

ti

llo n

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381378?tab=Overview

É

ch

Qui intègre donc Power Apps à Power BI!

Microsoft Power BI / Microsoft Excel BI

53/950

Vincent ISOZ

an

ti

llo n

Data sources

Tables manuelles

Cas classiques d'utilisations:

Tables de langues Tables de Pays Tables de fuseaux horaires Tables de phrases types traduites

ch

• • • •

É

Limitations courantes actuelles en cette année de 2019:

Microsoft Power BI / Microsoft Excel BI

54/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

55/950

É

ch

an

ti

llo n

Vincent ISOZ

Tables DAX On va prendre ici la table DAX la plus connue dans le monde Power BI: Microsoft Power BI / Microsoft Excel BI

56/950

llo n

Vincent ISOZ

ch

an

ti

Calendar = VAR FY_Month_Starts = 6 RETURN ADDCOLUMNS ( CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ), "Date As Integer", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Fiscal Year", YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < FY_Month_Starts, 1, 0 ), "Month number", FORMAT ( [Date], "MM" ), "Fiscal Month number", MOD ( MONTH ( [Date] ) - FY_Month_Starts, 12 ) + 1, "Year Month number", FORMAT ( [Date], "YYYY/MM" ), "Semester", "S" & IF ( MONTH ( [Date] ) <= 6, 1, 2 ), "Year Month Short", FORMAT ( [Date], "YYYY/mmm" ), "Month Name Short", FORMAT ( [Date], "mmm" ), "Month Name Long", FORMAT ( [Date], "mmmm" ), "Day Of Week Number", WEEKDAY ( [Date], 2 ), "Day Of Week", FORMAT ( [Date], "dddd" ), "Day Of Week Short", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "WeekN umber", "Week" & WEEKNUM ( [Date], 2 ), "Week", WEEKNUM ( [Date], 2 ), "ISO 8601 Week", WEEKNUM ( [Date], 21 ), "Year Quarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )

É

Soit:

Microsoft Power BI / Microsoft Excel BI

57/950

ch

an

ti

llo n

Vincent ISOZ

É

Ce qui donnera:

Microsoft Power BI / Microsoft Excel BI

58/950

ti

llo n

Vincent ISOZ

Rappelons les avantages d'avoir un table de calendrier plutôt que d'utiliser les décompositions automatiques qu'il y a dans chaque table:

an

1. Vous gagnez de la mémoire (plutôt qu'il y ait n colonnes de dates décomposées, il n'y en a qu'une seule qui est celle de la table de calendrier) 2. Vous ne pouvez pas personnaliser (ie ajouter des informations) aux colonnes de dates décomposées, alors qu'avec la table Calendrier, de nombreuses opportunités sont possibles!

ch

3. Comme vous avez une seule table de référence pour les dates, un seul slicer de dates (venant de la table calendrier!), suffira pour filtrer plusieurs tables différentes qui sont liées à la table calendrier (plutôt que d'avoir 1 slicer par colonne de date et par table!) 4. Vous aurez des erreurs si vous ajoutez dans un même visuel temporel des données de plusieurs tables dont il n'y pas de colonne de dates commune (ie une colonne de date de calendrier justement!)

É

Un bon exemple consiste à considérer le scénario avec les deux tables suivantes:

Microsoft Power BI / Microsoft Excel BI

59/950

llo n

Vincent ISOZ

ch

an

ti

Premièrement, vous ne pourrez pas avoir un slicer unique qui filtre les deux tables et… deuxièement si vous créez le visuel suivant dont avec les dates venant de la table Sales:

É

Alors les montants de Purchase Amount serront erronés. Alors qu'avec une table de Calendrier, vous aurez bien les bonnes valeurs!

Microsoft Power BI / Microsoft Excel BI

60/950

Microsoft SharePoint

ti

llo n

Vincent ISOZ

an

Bibliothèque d'images (Asset Library)

É

ch

Considérons la bibliothèque d'images suivante dans SharePoint Online:

Microsoft Power BI / Microsoft Excel BI

61/950

ch

an

ti

llo n

Vincent ISOZ

Nous voulons importer les URL dans Power BI.

É

Pour cela, nous importons un liste SharePoint Online:

Microsoft Power BI / Microsoft Excel BI

62/950

an

ti

llo n

Vincent ISOZ

É

ch

Ensuite on met l'adresse du site contenant la bibliothèque d'images:

Microsoft Power BI / Microsoft Excel BI

63/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

64/950

an

ti

llo n

Vincent ISOZ

É

ch

Après avoir nettoyé les colonnes inutiles, on obtient:

Microsoft Power BI / Microsoft Excel BI

65/950

an

ti

llo n

Vincent ISOZ

Liste d'images (liste personnalisée avec colonne Image)

É

ch

Considérons le scénario suivant:

Microsoft Power BI / Microsoft Excel BI

66/950

an

ti

llo n

Vincent ISOZ

É

ch

Ensuite dans Power BI on prend l'option d'importer une liste SharePoint:

Microsoft Power BI / Microsoft Excel BI

67/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

68/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

69/950

ti

llo n

Vincent ISOZ

ch

an

Ce qui donne après avoir supprimé les colonnes indésirables:

Microsoft Excel

Import d'un fichier Microsoft Excel stocké sur SharePoint

É

Considérons:

Microsoft Power BI / Microsoft Excel BI

70/950

an

ti

llo n

Vincent ISOZ

Nous voulons importer ce fichier.

É

ch

Pour ce faire à ce jour (fin 2019), il faudra passer par:

Microsoft Power BI / Microsoft Excel BI

71/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

72/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

73/950

É

ch

an

ti

llo n

Vincent ISOZ

Et voilà la suite est connue…

Microsoft Power BI / Microsoft Excel BI

74/950

Vincent ISOZ

É

ch

an

ti

llo n

Import (fusion) automatique de toutes les feuilles (incluant les futures) d'un classeur

Clic droit sur le dossier: Microsoft Power BI / Microsoft Excel BI

75/950

an

ti

llo n

Vincent ISOZ

É

ch

Ce qui donne:

Microsoft Power BI / Microsoft Excel BI

76/950

llo n

Vincent ISOZ

É

ch

an

ti

Et y'a plus qu'à, il faut qu'on…. Si jamais voici le code M correspondant:

Microsoft Power BI / Microsoft Excel BI

77/950

Vincent ISOZ

Import contenu bibliothéques SharePoint

an

ti

llo n

Considérons les fichiers CSV suivants dans une bibliothèque SharePoint 365:

É

ch

Ensuite dans Power BI:

Microsoft Power BI / Microsoft Excel BI

78/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

79/950

ch

an

ti

llo n

Vincent ISOZ

É

Le lien du site et non de la bibliothèque!!!!:

Microsoft Power BI / Microsoft Excel BI

80/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

81/950

É

ch

an

ti

llo n

Vincent ISOZ

On voit qu'il montre TOUS les fichiers de TOUTES les bibliothèque du site que nous avons choisi:

Microsoft Power BI / Microsoft Excel BI

82/950

an

ti

llo n

Vincent ISOZ

É

ch

On veut que trois des fichiers! Donc:

Microsoft Power BI / Microsoft Excel BI

83/950

an

ti

llo n

Vincent ISOZ

É

ch

Ce qui nous amène dans Power Query où on va filtrer en prenant que le dossier qui nous intéresse:

Après quoi on clique sur l'expandeur:

Microsoft Power BI / Microsoft Excel BI

84/950

É

ch

an

Ce qui nous amène à:

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

85/950

an

ti

llo n

Vincent ISOZ

É

ch

Ce qui nous amène à:

Et là il y a un piège au niveau du contenu des fichiers CSV que nous laissons le soin au lecteur d'identifier!

Microsoft Power BI / Microsoft Excel BI

86/950

Vincent ISOZ

É

ch

an

ti

llo n

JSON

Microsoft Power BI / Microsoft Excel BI

87/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

88/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

89/950

Vincent ISOZ

É

ch

an

ti

llo n

Ne rien changer à la boîte de dialogue qui apparît:

Microsoft Power BI / Microsoft Excel BI

90/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

91/950

ti

É

ch

an

Microsoft SQL Server

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

92/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

93/950

Vincent ISOZ

É

ch

an

ti

llo n

DirectQuery

Microsoft Power BI / Microsoft Excel BI

94/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

95/950

an

ti

llo n

Vincent ISOZ

É

ch

Depuis fin 2018:

Microsoft Power BI / Microsoft Excel BI

96/950

Vincent ISOZ

É

ch

an

ti

llo n

Query Folding

On clique sur Edit et on arrive dans Power Query pour noter que l'option:

Microsoft Power BI / Microsoft Excel BI

97/950

É

ch

an

ti

llo n

Vincent ISOZ

Si on supprime quelques colonnes et qu'on filtre sur les années 2009, cela donnera:

Microsoft Power BI / Microsoft Excel BI

98/950

ti

llo n

Vincent ISOZ

É

ch

an

Soit:

Microsoft Power BI / Microsoft Excel BI

99/950

an

ti

llo n

Vincent ISOZ

É

ch

Notez que certaines transformations dans Power Query désactivent automtiquement le Query Folding car il n'existe pas d'équivalent en SQL!

Microsoft Power BI / Microsoft Excel BI

100/950

Vincent ISOZ

É

ch

an

ti

llo n

OData

Microsoft Power BI / Microsoft Excel BI

101/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

102/950

an

ti

llo n

Vincent ISOZ

É

ch

Shift+Flèche vers le bas pour sélectionner rapidement:

Microsoft Power BI / Microsoft Excel BI

103/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

104/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

105/950

Vincent ISOZ

Dataflow Les DataFlows sont des processus Power Query qui s'exécutent dans le cloud indépendamment de tout rapport Power BI:

É

ch

an

ti

llo n

Les tables réutilisables ou les requêtes sur plusieurs fichiers Power BI sont l’un des meilleurs candidats pour Dataflow:

Microsoft Power BI / Microsoft Excel BI

106/950

É

ch

an

En compte admin:

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

107/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

108/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

109/950

É

ch

an

ti

llo n

Vincent ISOZ

On a alors l'éditeur Power Query Online qui s'ouvre:

Microsoft Power BI / Microsoft Excel BI

110/950

ti

llo n

Vincent ISOZ

an

On clique sur Done et on arrive sur:

É

ch

On peut sauvegarder:

Microsoft Power BI / Microsoft Excel BI

111/950

Vincent ISOZ

llo n

Streaming Dataset

Limitations actuelle (juin 2018): 75 colonnes au maximum

•

75 tables au maximum

•

10 000 lignes au maximum par demande de lignes POST uniques

•

1 000 000 lignes ajoutées par heure et par jeu de données

•

5 demandes de lignes POST en attente au maximum par jeu de données

•

120 demandes de lignes POST par minute et par jeu de données

•

Avec un tableau de 250 000 lignes ou plus, 120 demandes de lignes POST par heure et par jeu de données

•

200 000 lignes au maximum stockées par table dans un jeu de données FIFO

•

5 000 000 lignes au maximum stockées par table dans un jeu de données « sans stratégie de rétention »

•

4 000 caractères par valeur de colonne de type chaîne dans une opération de lignes POST

É

ch

an

ti

•

Microsoft Power BI / Microsoft Excel BI

112/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

113/950

ch

an

ti

llo n

Vincent ISOZ

É

Au bas de la page, vous voyez une option Historic Data Analysis. Lorsque cette option est désactivée, cela signifie que le stockage en continu des jeux de données est stocké dans un cache temporaire pendant un certain temps. Mais lorsque nous activons cette option, ces données vont commencer à stocker. On clique sur Create et on obtient:

Microsoft Power BI / Microsoft Excel BI

114/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

115/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

116/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

117/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

118/950

an

Imports Newtonsoft.Json

ti

llo n

Vincent ISOZ

Imports System.Net.Http Module Module1

É

ch

Public client As HttpClient = New HttpClient() Sub Main() Dim JsonDt As String Dim timeFormat As String = "yyyy-MM-ddTHH:mm:ss.fffZ" Do While 1 = 1 Dim RandomSpeed As Integer Dim Generator As System.Random = New System.Random() RandomSpeed = Generator.Next(10, 50) JsonDt = "[{""Speed"":" & RandomSpeed.ToString JsonDt = JsonDt + ",""Date"":""" & Date.UtcNow().ToString(timeFormat) JsonDt = JsonDt & """}]" Dim content As HttpContent = New StringContent(JsonDt) Dim url As New Uri("https://api.powerbi.com/beta/f6a89ff9-3073-4e72-a603-d9456ebee844/datasets/9d8b59 be-0c77-4486-aed3-xxxxx/rows?key=xxxxx_yourkey_xxxx ") Dim response As HttpResponseMessage response = client.PostAsync(url, content).Result System.Threading.Thread.Sleep(1000) Console.WriteLine(JsonDt.ToString) Loop End Sub End Module

Microsoft Power BI / Microsoft Excel BI

119/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

120/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

121/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

122/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

123/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

124/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

125/950

Vincent ISOZ

an

ti

llo n

Import vs DirectQuery vs Live Connection

Attention se rappeler que:

DirectQuery ne fonctionne pas pour Microsoft Access et une fois un jeu de données en mode Import, impossible à ce jour de la basculer en mode DirecQuery

•

En mode DirectQuery l'affichage Data View de Power BI n'est plus disponible (il n'y a plus que l'affichage des relations et des dashboards)

ch

•

En mode Live Connection les affichages Data View ET Relationships ne sont plus disponibles (il n'y a plus que l'affichage des dashboards qui est disponible)

•

Le langage DAX est très limité en mode Direct Query car la majorité des fonctions y existante ne peuvent être "traduites" dans le langage de la technologie à laquelle on est connectés! Et même certaines fonction comme format( ) qui existent pourtant dans SQL Server ne sont pas comprises par SQL Server…

É

•

Et plus en détails:

Microsoft Power BI / Microsoft Excel BI

126/950

llo n

Vincent ISOZ

É

ch

an

ti

Ou un autre résumé:

Microsoft Power BI / Microsoft Excel BI

127/950

Vincent ISOZ

Import

DirectQuery

Size

Up to 10 GB per dataset

No limitation

Data Source

Import data from Multiple sources

Data must come from a single Source

Performance

High-performance query engine

Depends on the data source response for each query

Data Change in the underlying data

Not Reflected. Required to do a Manual refresh in Power BI Desktop and republish the report or Schedule Refresh

Power BI caches the data for better performance. So, it is necessary to Refresh to ensure the latest data

Schedule Refresh

Maximum 8 schedules per day

Schedule often as every 15 mins

Power BI Gateway

Only required to get latest data from On-premise data sources

Must require to get data from Onpremise data sources

Data Transformations

Supports all transformations

Supports many data transformations with some limitations

an

ti

llo n

Capability

Data Modelling

No limitation

Some limitations such as autodetect relationships between tables and relationships are limited to a single direction.

Built-in Date Hierarchy

Available

Not available

Supports all DAX functions

Clustering

Available

Not available

Calculated Tables

Available

Not supported

Quick Insights

Available

Not available

Q&A

Available

Not available

Change Data Connectivity mode

Not possible to change Import to DirectQuery

Possible to change DirectQuery to Import

É

ch DAX expressions

Restricted to use complex DAX functions such as Time Intelligence functions. However, if there is a Date table available in the underlying source then it supports

Microsoft Power BI / Microsoft Excel BI

128/950

Vincent ISOZ

É

ch

an

ti

llo n

Data Flows (flux de données)

Microsoft Power BI / Microsoft Excel BI

129/950

Vincent ISOZ

Gateways

É

ch

an

ti

llo n

D'abord comprendre les différences:

Microsoft Power BI / Microsoft Excel BI

130/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

131/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

132/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

133/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

134/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

135/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

136/950

ti

llo n

Vincent ISOZ

É

ch

an

Il faut être dans un domaine pour la suite!

Microsoft Power BI / Microsoft Excel BI

137/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

138/950

an

ti

llo n

Vincent ISOZ

É

ch

Pour supprimer un gateway il y un bouton subtilement visible avec "…" sur lequel il faut cliquer:

Microsoft Power BI / Microsoft Excel BI

139/950

Vincent ISOZ

É

ch

an

ti

llo n

15. Refresh (mises à jour des données)

Microsoft Power BI / Microsoft Excel BI

140/950

Vincent ISOZ

É

ch

an

ti

llo n

Mises à jour des tables dans Power Query

Microsoft Power BI / Microsoft Excel BI

141/950

Vincent ISOZ

Mises à jour des tables dans Power BI Desktop Mises à jour manuelle de toutes les tables

llo n

Bon ce point il n'a pas grande chose à dire à ce jour:

Mises à jour manuelle de certaines tables

ch

an

ti

Pour mettre à jour seulement certaines tables dans Power BI, même en mi-2020 il faut encore aller dans la seule vue Model où la sélection de tables multiples est autorisée:

É

Une fois ceci fait, il suffit sur le panneau de droite de sélectionner avec la touche Ctrl les tables à mettre à jour et ensuite faire un clic droit:

Microsoft Power BI / Microsoft Excel BI

142/950

ti

Mises à jour automatique

llo n

Vincent ISOZ

an

Dans Power BI Desktop cette dernière ne marche (et l'option n'apparaît que dans cette condition) que si on travaille en mode Direct Query.

É

ch

Considérons alors que nous nous sommes connectés à la base suivante:

Une fois les tables importées cliques sur la partie vide d'un page et dans le panneau de propriétés vous trouverez alors:

Microsoft Power BI / Microsoft Excel BI

143/950

an

ti

llo n

Vincent ISOZ

É

ch

Si on active l'option, nous avons alors:

Et au niveau des options de durées, c'est tout à fait acceptable:

Microsoft Power BI / Microsoft Excel BI

144/950

É

ch

an

ti

Mises à jour incrémentielles

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

145/950

Vincent ISOZ

Mises à jour dans Power BI Service

É

ch

an

ti

Mises à jour automatiques

llo n

Mises à jour manuelles

Microsoft Power BI / Microsoft Excel BI

146/950

Vincent ISOZ

É

ch

an

ti

llo n

16. Data Mashup (Power Query)

Microsoft Power BI / Microsoft Excel BI

147/950

Vincent ISOZ

Commentaires M Une tâche importante du Data Scientists et du Quant est de documenter ses codes et procédures en conformité avec les exigences légales et normatives de son domaine d'activité.

É

ch

an

ti

llo n

Pour écrire une ligne de commentaire en M, on commencera la ligne par un double slash //:

Microsoft Power BI / Microsoft Excel BI

148/950

Vincent ISOZ

Icônes Power Query

Logo

Description Une simple table

llo n

Il faut parfois être bien concentré avec Power BI ou Microsoft Excel pour comprendre à quel type de symbole Power Query nous avons affaire. Voici quelques symboles qu'il faut bien comprendre:

Une table pour laquelle le chargement a été désactivé Un simple dossier

Avertissement à traiter!

ti

Une table non accédée depuis longtemps Une liste (table générée en pure M) Fonction M

an

Paramètre

Groupe (hiérarchie)

É

ch

Tableau 1 Tableau icônes Power Query

Microsoft Power BI / Microsoft Excel BI

149/950

Vincent ISOZ

É

ch

an

ti

llo n

Lister toutes les fonctions

Microsoft Power BI / Microsoft Excel BI

150/950

ch

an

ti

llo n

Vincent ISOZ

É

Ensuite le côté très utile c'est qu'on peut cliquer sur le mot Function pour tester certaines fonctions de façon interactive:

Microsoft Power BI / Microsoft Excel BI

151/950

ch

an

ti

llo n

Vincent ISOZ

É

N'oubliez pas que comme tout logiciel, Power Query n'a pas encore des fonctions pour absolument tout. Par exemple, il n'a pas de fonction d'arrondi aux 5 centimes les plus proches (arrondi au multiple) à ce jour (début 2020). Il faudra donc recourrir à la même vieille technique que dans Microsoft Excel:

Microsoft Power BI / Microsoft Excel BI

152/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

153/950

Vincent ISOZ

Tri sur colonnes multiples Nous savons qu'il n'est toujours pas possible à ce jour (2020) de trier dans Power BI un visuel de type table par de multiples colonnes.

llo n

Pour y arriver il faudra faire le tri dans Power Query (effectivement, rappelez-vous que par défaut Power BI prend le tri de Power Query dans l'affichage des rapports).

É

ch

an

ti

Considérons la table suivante dans Power Query comme base de travail:

Microsoft Power BI / Microsoft Excel BI

154/950

Vincent ISOZ

Considérons qu'un veuille trier par: 1. Activité (ordre croissant) 2. Article (orcre croissant) 3. Et enfin par Quantité (ordre croissant)

É

ch

an

Ce qui donnera bien:

ti

llo n

Pour faire cela on devra passer dans le code M et ajouter:

Microsoft Power BI / Microsoft Excel BI

155/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

156/950

Vincent ISOZ

Construction d'un VLOOKUP approximatif

an

ti

llo n

Considérons la table des ventes suivantes importée:

É

ch

Et la table des remises suivante:

Microsoft Power BI / Microsoft Excel BI

157/950

ti

llo n

Vincent ISOZ

an

Le but est d'appliquer la Remise de gros relativement à la colonne des Prix total avec rabais de la table des Ventes, relativement à la valeur la plus proche de la colonne Valeur Commande.

É

ch

Pour ce faire on va d'abord faire le Merge suivant:

Microsoft Power BI / Microsoft Excel BI

158/950

an

ti

llo n

Vincent ISOZ

É

ch

Notez bien la sélection d'une liaison de type Full Outer! On valide par OK pour obtenir:

Microsoft Power BI / Microsoft Excel BI

159/950

an

ti

llo n

Vincent ISOZ

É

ch

Ensuite on étend les colonnes pour obtenir:

Ce qui fait qu'on obtient bien évidemment quelque chose du genre suivant:

Microsoft Power BI / Microsoft Excel BI

160/950

ch

an

ti

llo n

Vincent ISOZ

É

Pour comprendre ce qui va suivre, il est conseillé de trier la table de la façon suivante:

Microsoft Power BI / Microsoft Excel BI

161/950

llo n

Vincent ISOZ

É

ch

an

ti

Pour obtenir:

Microsoft Power BI / Microsoft Excel BI

162/950

ch

an

ti

llo n

Vincent ISOZ

É

Maintenant la première subtilité vient à l'étape suivante! On ajoute une colonne avec la condition suivante:

Microsoft Power BI / Microsoft Excel BI

163/950

llo n

Vincent ISOZ

an

ti

On valide par OK et la deuxième subtilité c'est de trier immédiatement cette colonne:

É

ch

Et observons avant la touche finale:

Et aussi la zone suivante:

Microsoft Power BI / Microsoft Excel BI

164/950

llo n

Vincent ISOZ

an

ti

Et la zone suivante:

É

ch

Et ainsi de suite… Donc maintenant nous pouvons utiliser la dernière subtilité qui consiste à sélectionner la colonne Remise de gros et à prendre la transformation:

Ce qui nous donne:

Microsoft Power BI / Microsoft Excel BI

165/950

llo n

Vincent ISOZ

É

ch

an

ti

Et aussi:

Microsoft Power BI / Microsoft Excel BI

166/950

Vincent ISOZ

llo n

Et aussi:

É

ch

an

ti

Ensuite y'a plus qu'à supprimer les deux colonnes inutiles et faire les calculs habituels

Microsoft Power BI / Microsoft Excel BI

167/950

Vincent ISOZ

Utiliser des variables Avec certains systèmes informatiques que Business Warehouse (BW), les colonnes sont systématiquement renommée dans le code M de Power BI même quand la requête ne change pas.

É

ch

an

ti

llo n

À ce moment là, le mieux est de savoir qu'on peut en langage M (sans surprises), créer des variables (ie des constantes dans le cas présent!) pour les colonnes, filtres, rechercher/remplacer etc. comme l'illustre par example le simple filtre suivant:

Microsoft Power BI / Microsoft Excel BI

168/950

Vincent ISOZ

Fusion de feuilles multiples d'un unique fichier Microsoft Excel

llo n

On sait que la technique classique de fusion de plusieurs feuilles d'un même fichier Microsoft Excel est particulièrement laborieuse dans Power Query si on fait tout manuellement (surtout dès le moment où de nouvelles feuilles sont créées!).

an

ti

Il s'agit d'écrire dans le Power Query de Power BI:

É

ch

Soit en zoomant un peu:

Microsoft Power BI / Microsoft Excel BI

169/950

Vincent ISOZ

Traîtement batch d'un dossier de fichiers Microsoft Excel avec feuilles identiquement nommées

É

ch

an

ti

llo n

Considérones les trois fichiers suivants:

Microsoft Power BI / Microsoft Excel BI

170/950

É

ch

an

ti

llo n

Vincent ISOZ

Ce que nous allons devoir faire (à ce jour en l'état actuel de Power BI) est: Microsoft Power BI / Microsoft Excel BI

171/950

Vincent ISOZ

Importer un unique classeur et le traiter

•

Convertir le code M correspondant en fonction M

•

Importer tous les fichiers (utilisant la fonction M précédemment créée)

•

Combiner le tout

llo n

•

É

ch

an

ti

On commence:

Microsoft Power BI / Microsoft Excel BI

172/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

173/950

an

ti

llo n

Vincent ISOZ

É

ch

Ce qui donne:

On fait le nettoyage standard: Microsoft Power BI / Microsoft Excel BI

174/950

llo n

Vincent ISOZ

ch

an

ti

Soit le code M correspondant:

É

Maintenant nous allons convertir cela en fonction M en le changeant en:

Microsoft Power BI / Microsoft Excel BI

175/950

llo n

Vincent ISOZ

É

ch

an

ti

Ce qui devient alors effectivement une fonction:

On va renommer la fonction:

Microsoft Power BI / Microsoft Excel BI

176/950

llo n

Vincent ISOZ

É

ch

an

ti

Ok maintenant tout est prêt. On va pouvoir importer le dossier avec les trois fichiers et appliquer la fonction que l'on vient de créer dessus! Pour cela:

Microsoft Power BI / Microsoft Excel BI

177/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

178/950

llo n

Vincent ISOZ

an

ti

Ce qui nous amène à:

ch

Maintenant, normalement, nous serions tentés de cliquer sur les doubles flèches à côté de l'entête Content pour les combiner toutes… mais cela ne fonctionne pas ici (il renvoie simplement le premier fichier lorsque nous travaillons avec des fichiers Excel.) C'est donc là que nous devons recourir à notre fonction!

É

Pour cela:

Microsoft Power BI / Microsoft Excel BI

179/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

180/950

ch

an

Ce qui donne:

ti

llo n

Vincent ISOZ

É

Reste plus qu'à faire le nettoyage habituel…

Microsoft Power BI / Microsoft Excel BI

181/950

Vincent ISOZ

ch

an

ti

llo n

Fusion de fichiers Microsoft Excel d'un dossier avec feuille unique non identiquement nommées

Soit, pour ceux qui préfère copier/coller que tout récrire: let

É

Source = Folder.Files("C:\Users\Vincent Isoz\Documents\Professionel\Cours\MSOfficePowerQuery\Exercises_PowerQuery_FR\ ImportDossier\XLSX_AvecPiege"), #"Removed Columns" = Table.RemoveColumns(Source,{"Content", "Date accessed", "Date modified", "Date created", "Attributes"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Extension] = ".xlsx"), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "FullFileName", each [Folder Path] & [Name]), #"Remove other columns" = Table.RemoveColumns(#"Added Custom",{"Name", "Extension", "Folder Path"}),

Microsoft Power BI / Microsoft Excel BI

182/950

Vincent ISOZ

É

ch

an

ti

llo n

//La subtilité se trouve ici avec l'utilisation de "each Excel Workbook(...)" #"Invoked Custom Function" = Table.AddColumn(#"Remove other columns", "GetSheetName", each Excel.Workbook(File.Contents([FullFileName]), null, true)), #"Retrieve Data Object" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetSheetName", {"Data"}, {"GetSheetName.Data"}), #"Expand Data Columns" = Table.ExpandTableColumn(#"Retrieve Data Object", "GetSheetName.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"GetSheetName.Data.Column1", "GetSheetName.Data.Column2", "GetSheetName.Data.Column3", "GetSheetName.Data.Column4", "GetSheetName.Data.Column5", "GetSheetName.Data.Column6", "GetSheetName.Data.Column7"}), //À partir d'ici c'est à nouveau du classique #"Colonnes supprimées" = Table.RemoveColumns(#"Expand Data Columns",{"FullFileName"}), #"En-têtes promus" = Table.PromoteHeaders(#"Colonnes supprimées", [PromoteAllScalars=true]), #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"idSorties", type any}, {"VendeursId", type any}, {"ClientsId", type any}, {"strNbArticle", type text}, {"intUnites", type any}, {"bolTypPay", type any}, {"datDateOut", type any}}), #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([idSorties] <> "idSorties")), #"Lignes vides supprimées" = Table.SelectRows(#"Lignes filtrées", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Doublons supprimés" = Table.Distinct(#"Lignes vides supprimées") in #"Doublons supprimés"

Microsoft Power BI / Microsoft Excel BI

183/950

Vincent ISOZ

Fusionner un dossier avec problèmes d'en-têtes de colonnes

ch

an

ti

llo n

Considérons un dossier avec les trois fichiers suivants (cela pourrait aussi être des fichier Microsoft Excel):

É

Si nous l'importons de façon classique:

Microsoft Power BI / Microsoft Excel BI

184/950

ch

an

ti

llo n

Vincent ISOZ

É

Nous obtenons automatiquement le résultats problématique suivant:

Microsoft Power BI / Microsoft Excel BI

185/950

Vincent ISOZ

llo n

Pour résoudre ce problème on d'abord dans la requête Transforme Sample File et on supprime Promoted Headers:

an

ti

Ce qui donnera:

É

ch

Ensuite on retourne dans la requête principale que aura une erreur pour le coup:

Et il suffit juste de supprimer la dernière étape:

Microsoft Power BI / Microsoft Excel BI

186/950

llo n

Vincent ISOZ

ch

an

ti

Et on y est! On obtient:

É

La suite c'est du classique Power Query…

Microsoft Power BI / Microsoft Excel BI

187/950

Vincent ISOZ

Ajouter la date de dernières mise à jour sur un rapport Ceci n'est pas possible à notre connaissance avec une fonction DAX puisque ces dernières se mettent à jour automatiquemnt dès que l'on change de page de rapport.

llo n

L'idée est alors plutôt d'inclure une date dans une table PowerQuery! Mais à cause des des fuseaux horaires vous risquez de rencontrer de nombreuses difficultés dès que vous publierez sur le web. Une solution simple est alors d'utiliser une fonction de temps universelle.

É

ch

an

ti

Pour voir comment procéder, créez la table manuelle suivante dans PowerQuery:

Une fois ceci fait, ajoutez une colonne:

Microsoft Power BI / Microsoft Excel BI

188/950

É

ch

an

ti

llo n

Vincent ISOZ

Et n'oubliez pas de changer le type:

Microsoft Power BI / Microsoft Excel BI

189/950

llo n

Vincent ISOZ

an

ti

Ce qui donnera:

É

ch

Et voilà il vous suffit d'ajouter cette colonne dans un visuel de type Card d'une page de rapport et le tour est joué!

Microsoft Power BI / Microsoft Excel BI

190/950

Vincent ISOZ

Supprimer automatiquement toutes les lignes après une ligne particulière

ch

an

ti

llo n

Considérons la table importée suivante:

É

Après quelques nettoyages triviaux et l'ajout d'une colonne d'Index, nous avons:

Microsoft Power BI / Microsoft Excel BI

191/950

É

ch

an

ti

llo n

Vincent ISOZ

Et maintenant il s'agit de modifier le code M comme ci-dessous:

Microsoft Power BI / Microsoft Excel BI

192/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

193/950

Vincent ISOZ

Création d'une Table de calendrier simple avec durée en M Nous savons qu'à ce jour il n'est pas possible de créer un table calendrier en DAX dans un fichier Power BI en direct query. D'où la procédure qui va suivre!

an

ti

llo n

La fonction duration( ) en M ci-dessous a pour paramètres jours, heures, minutes, secondes.

É

ch

Ou une version alternative:

Microsoft Power BI / Microsoft Excel BI

194/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

195/950

Vincent ISOZ

Création d'une Table de calendrier avec étendue fixe en M

É

ch

an

ti

llo n

Nous savons qu'à ce jour il n'est pas possible de créer un table calendrier en DAX dans un fichier Power BI en direct query. D'où la procédure qui va suivre!

Microsoft Power BI / Microsoft Excel BI

196/950

Vincent ISOZ

Création d'une Table de calendrier avec variable en M Nous savons qu'à ce jour il n'est pas possible de créer un table calendrier en DAX dans un fichier Power BI en direct query. D'où la procédure qui va suivre!

É

ch

an

ti

llo n

Cela peut être très utile pour ceux qui utilisent des fichiers Power BI avec Direct Query:

Microsoft Power BI / Microsoft Excel BI

197/950

Vincent ISOZ

Création d'une Table de calendrier complète en M Pour avoir un table de calendrier relativement complète dans Power Query, collez le code suivant dans une requête M:

ch

an

ti

llo n

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table => let DayCount = Duration.Days(Duration.From(EndDate - StartDate)), Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])), InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])), InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])), InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])), InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]), InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text), InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])), InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])), InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])), InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text), InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date) in InsertWeekEnding in CreateDateTable

É

Ce qui donnera:

Microsoft Power BI / Microsoft Excel BI

198/950

an

ti

llo n

Vincent ISOZ

ch

Une fois validé:

É

Ce qui donnera (seulement une partie de la table est visible):

Microsoft Power BI / Microsoft Excel BI

199/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

200/950

Vincent ISOZ

Filtre dynamique en M

an

ti

llo n

Considérons la table suivante importée dans Power Query:

Une demande relativement fréquent est: Comment filtrer dynamique cette table sur l'année la plus grande (ie la dernière année).

É

ch

La réponse consiste à écrire le code M suivant:

Microsoft Power BI / Microsoft Excel BI

201/950

É

ch

an

Ce qui donne bien:

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

202/950

Vincent ISOZ

É

ch

an

ti

llo n

Création d'une Fonction simple en M

Microsoft Power BI / Microsoft Excel BI

203/950

Vincent ISOZ

É

ch

an

ti

llo n

Création d'une Fonction avec arguments optionnels en M

Microsoft Power BI / Microsoft Excel BI

204/950

Vincent ISOZ

É

ch

an

ti

llo n

Création d'une Fonction avec arguments optionnels et typés en M

Microsoft Power BI / Microsoft Excel BI

205/950

Vincent ISOZ

Fonction de nettoyage d'espaces (pour Power Query Excel seulement!) Ceux qui connaissent déjà un peu Power Query, savent combien il est frustrant d'utiliser la fonction trim du language M puisqu'elle diffère de celle d'Excel. Effectivement: Fonction

Résultat

Microsoft Excel

=TRIM(“ trim me “)

“trim me”

Power Query

=Text.Trim(“ trim me “)

“trim me“

llo n

Source

É

ch

an

ti

Le nettoyage des caractères d'espaces à double se fait automatiquement dans le Power Query de Power BI à la différence de celui de Microsoft Excel...! (à ce jour du moins...).

Microsoft Power BI / Microsoft Excel BI

206/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

207/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

208/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

209/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

210/950

Vincent ISOZ

É

ch

an

ti

llo n

Création d'une Liste numérique en M

Microsoft Power BI / Microsoft Excel BI

211/950

Vincent ISOZ

É

ch

an

ti

llo n

Création d'une Séquence numérique en M

Microsoft Power BI / Microsoft Excel BI

212/950

Vincent ISOZ

É

ch

an

ti

llo n

Création d'une Séquence numérique en M avec pas spécifique

Microsoft Power BI / Microsoft Excel BI

213/950

Vincent ISOZ

Création d'une Liste statique en M

ch

an

ti

llo n

Typique pour créer un annuaire alphabétique sur les Dashboards:

É

Sinon on peut aussi créer une simple liste de la manière suivante:

Microsoft Power BI / Microsoft Excel BI

214/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

215/950

Vincent ISOZ

ch

an

ti

llo n

Création d'une Table statique en M

É

Mais la syntaxe suivante passe aussi:

Microsoft Power BI / Microsoft Excel BI

216/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

217/950

Vincent ISOZ

Création d'une Table dynamique en M

É

ch

an

Ce qui donne après validation:

ti

llo n

Une création d'une table dynamique avec le code M suivant:

Si on saisit des valeurs:

Microsoft Power BI / Microsoft Excel BI

218/950

llo n

Vincent ISOZ

É

ch

an

ti

Cela donne:

Microsoft Power BI / Microsoft Excel BI

219/950

Vincent ISOZ

Création d'un simple colonne de valeurs aléatoires

an

ti

llo n

Souvent utile pour faire des tests par des consultants (principalement). On écrira simplement dans une nouvelle requête vierge:

ch

Attention à ne pas oublier à transfomer en tant que Table ensuite, si vous souhaite en faire un usage normal dans Power BI…

É

Remarque: La fonction List.Random( ), possède en tant que deuxième paramètre, un seed pour information!

Microsoft Power BI / Microsoft Excel BI

220/950

Vincent ISOZ

Ajout d'une colonne de valeurs aléatoires numériques

an

ti

llo n

C'est très souvent utile pour faire des tests. Pour cela considérons que d'abord nous avons par exemple créé une table de calendrier typiquement avec:

É

ch

D'abord pour pouvoir ajouter une colonne, il va falloir transformer la liste en une table en utilisant le bouton To Table:

Microsoft Power BI / Microsoft Excel BI

221/950

an

ti

llo n

Vincent ISOZ

É

ch

Une fois la liste transformée en table, nous pouvons cliquer sur Custom Column:

Microsoft Power BI / Microsoft Excel BI

222/950

ch

an

ti

llo n

Vincent ISOZ

É

On aura alors:

Microsoft Power BI / Microsoft Excel BI

223/950

É

ch

an

ti

llo n

Vincent ISOZ

A chaque rafraîchissement, les nombres aléatoires se regénéreront (il n'y pas pas de seed à ce jour pour cette fonction!).

Microsoft Power BI / Microsoft Excel BI

224/950

Vincent ISOZ

Création d'une colonne de valeurs aléatoires catégorielles

an

ti

llo n

Par exemple pour générer 1'000'000 de variables aléatoires catégorielles à partir de 3 valeurs prédéfinies:

É

ch

Ce qui donnera:

Microsoft Power BI / Microsoft Excel BI

225/950

Vincent ISOZ

Échantillonnage aléatoire simple

É

ch

an

Ajoutons-y une colonne aléatoire:

ti

llo n

Ici il s'agit simplement de la suite du cas précédent, car cette technique utilise la génération de nombres aléatoires (cependant, favorisez des outils scientifiques comme R et Python pour faire du "vrai" échantillonnage aléatoire!). Considérons la table importée suivante:

Microsoft Power BI / Microsoft Excel BI

226/950

an

ti

llo n

Vincent ISOZ

Rappel: La fonction List.Random( ), possède en tant que deuxième paramètre, un seed pour information!

É

ch

Nous validons et nous voyons bien que nous obtenons une liste avec un uniquement élément par cellule:

Microsoft Power BI / Microsoft Excel BI

227/950

ti

llo n

Vincent ISOZ

ch

an

Nous cliquons sur le bouton d'expansion et prenons Expand to New Rows:

É

Cela donnera:

Ce qui donnera alors le résultat attendu:

Microsoft Power BI / Microsoft Excel BI

228/950

ti

llo n

Vincent ISOZ

É

ch

an

Ensuite il suffit de trier sur la colonne aléatoire:

Microsoft Power BI / Microsoft Excel BI

229/950

Vincent ISOZ

En notant qu'à chaque fois que vous mettez à jour en cliquant sur Refresh Preview, alors vous aurez de nouvelles données triées!

É

ch

an

ti

llo n

Maintenant pour en sélectionner un nombre fini il suffira d'aller dans Keep Top Rows et de faire le choix qui vous convient le mieux:

Microsoft Power BI / Microsoft Excel BI

230/950

Vincent ISOZ

Traitements des dates

É

ch

an

ti

llo n

D'abord un résumé visuel du menu concernant la date:

Microsoft Power BI / Microsoft Excel BI

231/950

Vincent ISOZ

Création d'un paramètre pour chemin de fichiers (ou serveurs)

an

ti

llo n

Considérons tous les imports suivants:

ch

Imaginons qu'ils viennent tous d'un même dossier. Malheureusement nous savons par anticipation que le chemin de ce dossier peut changer! Nous souhaiterions faire en sorte que le changement soit gérable le plus simplement possible pour nous-mêmes et nos collègues! C'est là un cas typique et ultra-classique de l'utilisation des paramètres!

É

L'idée est de faire en sorte que tous les scripts M de toutes les requêtes:

Microsoft Power BI / Microsoft Excel BI

232/950

llo n

Vincent ISOZ

Pointent vers un paramètre unique centralisé de Power Query et mette à jour directement TOUS les chemins de TOUS les imports impactés par le problème de changement de dossier!

É

ch

an

ti

Pour cela on clique sur Manage Parameters et ensuite sur New Parameter:

Microsoft Power BI / Microsoft Excel BI

233/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

234/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

235/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

236/950

ti

llo n

Vincent ISOZ

É

ch

an

Sur la même idée, on peut créer des paramètres pour des filtres:

Microsoft Power BI / Microsoft Excel BI

237/950

an

ti

llo n

Vincent ISOZ

ch

Et les imbriquer dans le code M typiquement de la manière suivante: …" & SalesYear & "…

É

des statistiques:

Microsoft Power BI / Microsoft Excel BI

238/950

an

ti

llo n

Vincent ISOZ

ch

Et les imbriquer dans le code M typiquement de la manière suivante: …" & AggType & "…

É

etc.!

Microsoft Power BI / Microsoft Excel BI

239/950

Vincent ISOZ

Importer le fichier du jour

ch

an

ti

llo n

Considérons le script suivant d'import:

L'idée est de considéré que chaque jour nous avons un fichier avec la date du jour à la fin. Nous souhaiterions avoir un script Power Query gérant cela avec une variable.

É

Nous modifions alors le script comme visible ci-dessous:

Microsoft Power BI / Microsoft Excel BI

240/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

241/950

Vincent ISOZ

É

ch

an

ti

llo n

Compter les mots

Microsoft Power BI / Microsoft Excel BI

242/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

243/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

244/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

245/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

246/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

247/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

248/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

249/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

250/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

251/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

252/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

253/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

254/950

Vincent ISOZ

Extraire les premiers/derniers de groupes

ch

an

ti

llo n

Soit la table suivante:

On veut pour chaque article, sortir la dernière commande (en termes de Date Commande).

É

Pour cela, on va d'abord trier dans l'ordre décroissant de date:

Microsoft Power BI / Microsoft Excel BI

255/950

ti

an

Ensuite on fait un groupement:

llo n

Vincent ISOZ

É

ch

Avec les paramètres suivants:

On obtient alors: Microsoft Power BI / Microsoft Excel BI

256/950

an

ti

Pour la suite il va falloir aller dans l'éditeur M:

llo n

Vincent ISOZ

É

ch

Ce qui va afficher:

Microsoft Power BI / Microsoft Excel BI

257/950

É

ch

an

Qu'on va d'abord reformater ainsi:

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

258/950

an

ti

llo n

Vincent ISOZ

É

ch

Qu'on va devoir modifier comme suit:

Microsoft Power BI / Microsoft Excel BI

259/950

an

ti

llo n

Vincent ISOZ

Il faut prendre le type number, même si ce n'est pas un nombre!!!

ch

N'oubliez pas une fois terminé de changer le Type des colonnes comme à l'habitude!

É

On peut évidemment ajouter d'autres colonnes ainsi, mais le résultat donnera ce que nous voulions:

Microsoft Power BI / Microsoft Excel BI

260/950

É

ch

an

ti

Ensuite y'a plus qu'à il faut qu'on…

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

261/950

Vincent ISOZ

Numéroter des groupes Dans la famille de techniques similaires à celle d'avant, nous avons celle qui consiste à rajout un index numéroté mais avec la difficulté qui cet index doit recommencer sa numérotation à chaque groupement d'un variable catégorielle donnée (nominale ou ordinale).

ch

an

ti

llo n

Pour cela considérons à nouveau la table suivante:

É

On va d'abord trier par odre croissant des dates de commande:

Microsoft Power BI / Microsoft Excel BI

262/950

llo n

Vincent ISOZ

É

ch

an

ti

Ensuite on groupe:

De la manière suivante: Microsoft Power BI / Microsoft Excel BI

263/950

llo n

Vincent ISOZ

ch

an

ti

Ce qui donnera:

É

Ensuite on ajoute un nouvelle colonne:

Microsoft Power BI / Microsoft Excel BI

264/950

Vincent ISOZ

an

ti

llo n

Et on y met:

É

ch

Ce qui donnera:

Ensuite on garde seulement la dernière colonne:

Microsoft Power BI / Microsoft Excel BI

265/950

ch

an

Ce qui donnera évidemment:

ti

llo n

Vincent ISOZ

É

Ensuite on développe la colonne comme à l'habitude (on va prendre que quelques colonnes pour l'exemple!):

Microsoft Power BI / Microsoft Excel BI

266/950

an

ti

llo n

Vincent ISOZ

É

ch

Ce qui donnera au final:

Microsoft Power BI / Microsoft Excel BI

267/950

an

ti

llo n

Vincent ISOZ

É

ch

Et on peut alors vérifier qu'effectivement à chaque changement d'article nous avons bien l'index qui recommence sa numérotation et ceci en plus dans l'ordre croissant des dates:

Microsoft Power BI / Microsoft Excel BI

268/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

269/950

Vincent ISOZ

Fusionner de multiples lignes en une seule ligne

llo n

Considérons dans Power Query les deux table suivantes:

ch

an

ti

Et:

É

On veut créer une nouvelle table qui aura le contenu suivant:

Microsoft Power BI / Microsoft Excel BI

270/950

Vincent ISOZ

ti

llo n

Pour arriver à ce résultat on fait d'abord un merge comme à l'habitude:

É

ch

an

Ensuite vient la première subtilité! On rajoute une colonne avec la formule suivante:

Microsoft Power BI / Microsoft Excel BI

271/950

Vincent ISOZ

ti

llo n

Ce qui donnera:

É

ch

an

Ensuite, deuxième subtilité, on rajoute:

Microsoft Power BI / Microsoft Excel BI

272/950

Vincent ISOZ

ch

an

Ensuite on étend la colonne:

ti

llo n

Ce qui donnera:

É

On obtient finalement:

Microsoft Power BI / Microsoft Excel BI

273/950

llo n

Vincent ISOZ

É

ch

an

ti

Et il ne reste plus qu'à supprimer les colonnes indésirables et c'est terminé!

Microsoft Power BI / Microsoft Excel BI

274/950

Vincent ISOZ

Lag de colonnes

an

ti

llo n

Nous avons vu dans les cours MATLAB, R et Python de l'importance du lag de données pour calculer les log-return ou ROC en Finance. Il est assez normal vu le public visé par Power Query que ce soit aussi quelque chose de demandé!

É

ch

Pour arriver au résultat escompté, on doit rajouter une colonne d'index classique qui commence à partir de zéro (!):

Microsoft Power BI / Microsoft Excel BI

275/950

ch

an

ti

llo n

Vincent ISOZ

É

Ensuite nous allons dans l'éditeur avancé pour ajouter la ligne suivante juste en-dessous de l'ajout d'index:

Microsoft Power BI / Microsoft Excel BI

276/950

ti

É

ch

an

Ce qui donnera:

llo n

Vincent ISOZ

Attention! Il y a consensus que faire cela en DAX avec la fonction LOOKUPVALUE( ) est significativement plus rapide!

Microsoft Power BI / Microsoft Excel BI

277/950

Vincent ISOZ

É

ch

an

ti

llo n

Fuseaux horaires

Microsoft Power BI / Microsoft Excel BI

278/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

279/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

280/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

281/950

Vincent ISOZ

17. DAX (Data Analysis Expression)

an

ti

llo n

Voici comment on peut présenter DAX:

Gardez bien en tête les trois points suivants quand vous travaillez avec du DAX: 1. Certaines fonctions semblent faire doublon mais en réalité ce sont des évolutions historiques (pensez à LOOKUP, VLOOKUP et XLOOKUP dans Excel par exemple!)

ch

2. Certaines fonctions remplacent d'autres au niveau de leur comportement uniquement pour améliorer la performance (l'exemple le plus flagrant est REMOVEFILTERS qui a remplacé ALL en septembre 2019)

É

3. Certaines fonctions ne peuvent être réalisées (écrites) que si dès le début la bonne table est sélectionnée pour créer la mesure!

Microsoft Power BI / Microsoft Excel BI

282/950

Vincent ISOZ

Reverse Query (DAX) Un point important de DAX est de comprendre que nombreuses de ses fonctions sont en réalité des fonctions de requêtes comme l'est le SQL et aussi que certaines de ses fonctions, marchent uniquement dans Excel BI.

É

ch

an

ti

llo n

Notez aussi que de nombreux fonctions Analytiques DAX ont dans la pratique (business case) des fonctions de requête DAX à l'intérieur d'elles-mêmes, comme l'illustre par exemple ce calcul du taux internet de rentabilité (voir plus loin pour les détails):

Microsoft Power BI / Microsoft Excel BI

283/950

Vincent ISOZ

Exemple 1.: Requête EVALUATE Excel BI 2010 à 2019

llo n

La requête EVALUATE( ) ne marche pas dans Power BI à ce jour mais uniquement dans Microsoft Excel (et SSAS). Nous verrons plus tard un pseudo-équivalent qui marche dans Power BI.

É

ch

an

ti

Pour l'exemple considérons dans Excel Power BI la table suivante:

Microsoft Power BI / Microsoft Excel BI

284/950

ch

an

ti

llo n

Vincent ISOZ

É

Nous souhaiterions extraire tout cette table dans une feuille Microsoft Excel.

Microsoft Power BI / Microsoft Excel BI

285/950

Vincent ISOZ

Solution

ch

an

ti

llo n

Considérons la table des ventes suivante imposée sous le nom de connexion Excel SourcePrincipale:

É

Ensuite, revenons dans une feuille Excel et cliquons sur le bouton Existing Connections:

Microsoft Power BI / Microsoft Excel BI

286/950

llo n

Vincent ISOZ

É

ch

an

ti

Ce qui nous donne:

On prend la connexion Excel SourcePrincipale et nous validons par Open et dans la boîte de dialogue qui apparaît, nous prenons l'option Table:

Microsoft Power BI / Microsoft Excel BI

287/950

ch

an

ti

Nous validons par OK et nous obtenons:

llo n

Vincent ISOZ

É

Ensuite nous faisons un clic droit, pour obtenir choisir l'option Edit DAX:

Microsoft Power BI / Microsoft Excel BI

288/950

É

ch

an

ti

llo n

Vincent ISOZ

Et nous obtenons une petite boîte de dialogue où dans la liste déroulante, nous allons spécifier que nous voulons du DAX et non pas une simple table:

Microsoft Power BI / Microsoft Excel BI

289/950

llo n

Vincent ISOZ

ch

an

ti

Et ensuite nous y mettons la requête DAX qui renvoie simplement toute la table:

É

Ce qui donnera évidemment le même résultant:

Microsoft Power BI / Microsoft Excel BI

290/950

É

ch

an

Ou dans une version plus élaborée:

ti

llo n

Vincent ISOZ

Ce qui donnera:

Microsoft Power BI / Microsoft Excel BI

291/950

llo n

Vincent ISOZ

É

ch

an

ti

Notons que EVALUATE n'est pas à notre connaissance et à ce jour, géré par Power BI!

Microsoft Power BI / Microsoft Excel BI

292/950

Vincent ISOZ

Exemple 2.: Requête ALL Power BI 2018-03 / Excel 2010 à 2019

an

ti

llo n

Nous allons reproduire avec Power BI, l'exemple fait précédemment avec le DAX de Microsoft Excel. Pour cela nous partons de la même table importée dans Power BI:

É

ch

Ensuite, nous cliquons sur New Table:

Microsoft Power BI / Microsoft Excel BI

293/950

É

ch

an

ti

Et nous utilisons la requête ALL( ):

llo n

Vincent ISOZ

Mais on peut aussi prendre un sous-ensemble:

Microsoft Power BI / Microsoft Excel BI

294/950

an

ti

llo n

Vincent ISOZ

ch

Notez que ALL( ) par défaut, applique toujours une suppression des doublons par défaut (cela se voit particulièrement bien en faisant en mettant une seule colonne dans l'argument de ALL( ))!

É

L'équivalent dans Microsoft Excel sera bien évidemment:

Microsoft Power BI / Microsoft Excel BI

295/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

296/950

Vincent ISOZ

Exemple 3.: Requête SELECTCOLUMNS avec table unique Power BI 2018-03 / Excel 2010 à 2019

an

ti

Voyons d'abord un exemple avec Power BI:

llo n

La requête SELECTCOLUMNS( ) est la version restreinte de la requête ALL( ) à seulement quelques colonnes. Elle a cependant l'avantage de pouvoir renommer des colonnes à la volée!

É

ch

Et l'équivalent avec Microsoft Excel:

Microsoft Power BI / Microsoft Excel BI

297/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

298/950

Vincent ISOZ

Exemple 4.: Requête VALUES Power BI 2019-08 / Excel 2010 à 2019

É

ch

On part de:

an

ti

llo n

Nous allons voir ici une alternative à ALL( ) et SELECTCOLUMNS( ) (mais un cas particulier de ce dernier) que l'on retrouve souvent dans les formules DAX, qui est la fonction VALUES( ) qui en DAX est à de multiples usages:

Microsoft Power BI / Microsoft Excel BI

299/950

Vincent ISOZ

É

ch

an

Et nous écrivons:

ti

llo n

Ensuite, nous cliquons sur New Table:

Ce qui fait bien une copie par référence de la table d'origine! Ou bien pour une colonne particulière (cas le plus fréquent dans les formules DAX avancées!!!):

Microsoft Power BI / Microsoft Excel BI

300/950

ti

llo n

Vincent ISOZ

É

ch

an

Nous verrons plus loin comment éliminer les lignes vierges.

Microsoft Power BI / Microsoft Excel BI

301/950

Vincent ISOZ

Exemple 5.: Requête DISTINCT Power BI 2018-03 / Excel 2010 à 2019

ch

an

ti

llo n

La requête DISTINCT( ) comme son nom l'indique, permet de supprimer les doublons. Voyons un exemple avec Power BI:

É

Et son équivalent dans Microsoft Excel:

Microsoft Power BI / Microsoft Excel BI

302/950

ch

an

ti

llo n

Vincent ISOZ

Cette requête fonctionne donc aussi bien sur des tables que sur des requêtes!

É

Notez que nous pouvons faire la même chose que ce que nous avions fait avec la commande VALUES( ):

Microsoft Power BI / Microsoft Excel BI

303/950

llo n

Vincent ISOZ

Une différence important entre DISTINCT( ) et VALUES( ) et que dans le cas où ses fonctions sont utilisées sur une tables liée du côté "plusieurs" (avec relation "un à plusieurs"), et que la table du côté "un" n'a pas de valeur correspondant sur la clé, alors VALUES( ) renverra des lignes vides alors que DISTINCT( ) n'en renverra pas (excepté si la table

É

ch

an

ti

du côté "plusieurs" a vraiment physiquement des lignes vierges).

Microsoft Power BI / Microsoft Excel BI

304/950

Vincent ISOZ

Exemple 6.: Requête SELECTCOLUMNS( ) + DISTINCT( ) + AVERAGE( ) + ROUND( )

llo n

Power BI 2018-03 / Excel 2010 à 2019

Curieusement… il ne semble pas simple avec DAX (contrairement à SQL) d'extraire une simple scalaire avec une requête. Effectivement, pous un simple calcul de moyenne globable, voyez plutôt… :

É

ch

an

ti

Soyez cependant très prudent avec ce type de calculs dans des requêtes plus complexes. Vous pourriez effectivement avoir des surprises en vérifiant les résultats numériques si vous en faites pas attention!!!

Microsoft Power BI / Microsoft Excel BI

305/950

Vincent ISOZ

Exemple 7.: Requête CALCULATETABLE Power BI 2018-03 / Excel 2010 à 2019

É

ch

an

ti

llo n

La requête CALCULATETABLE( ) permet d'extraire tout une table mais de filtrer cette dernière avec un ou plusieurs filtres. Voyons cela avec Power BI:

Avec Microsoft Excel cela donne:

Microsoft Power BI / Microsoft Excel BI

306/950

an

ti

llo n

Vincent ISOZ

ch

Sinon on peut faire des requêtes avec critères multiples ET:

É

Ou avec une condition OU:

Ou avec une condition IN:

Microsoft Power BI / Microsoft Excel BI

307/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

308/950

Vincent ISOZ

Exemple 8.: Requête FILTER Power BI 2018-03 / Excel 2010 à 2019

llo n

La requête FILTER( ) permet d'extraire tout une table mais de filtrer cette dernière avec un ou plusieurs filtres au même titre que'avec CALCULATETABLE( ) mais la première serait plus rapide selon l'article très détaillé de Marco Russo:

https://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/ Nous souhaiterions filter tous les articles IBM 500 ou AST Intel 150 et dont la facture payée est à l'état Oui.

É

ch

an

ti

Pour cela, nous écrirons:

Microsoft Power BI / Microsoft Excel BI

309/950

Vincent ISOZ

Exemple 9.: Requête SAMPLE Power BI 2018-03 / Excel 2010 à 2019

llo n

C'est une requête très simple. On peut parfois douter de son utilité mais en réalité…. si on mélange à la table d'origine une colonne de variables aléatoires automatiquement générées, on obtient un outil d'échantillon et de simulation de population très intéressant.

ch

an

ti

Pour voir en quoi elle consiste, un simple exemple suffit:

É

Remarque: Visiblement, il y a un "seed", et c'est la même chose pour les fonctions qui génèrent des nombres aléatoires en DAX sur des colonnes!

Microsoft Power BI / Microsoft Excel BI

310/950

Vincent ISOZ

Exemple 10.: Requête SUMMARIZE Power BI 2018-03 / Excel 2010 à 2019

an

ti

llo n

Le but de la requête SUMMARIZE( ) est de faire un groupement simple comme l'illustre l'exemple suivant:

Il s'agit donc d'une requête d'une forme aussi de requête de distinction dans sa forme basique mais elle peut faire beaucoup plus comme nous allons le voir juste après!

É

ch

L'équivalent dans Microsoft Excel étant:

Mais on peut aussi faire des statistiques de regroupement: Microsoft Power BI / Microsoft Excel BI

311/950

an

ti

llo n

Vincent ISOZ

É

ch

Ou son équivalent dans Microsoft Excel:

Notons aussi l'exemple très important suivant qui nous sera utiles pour plus tard:

Microsoft Power BI / Microsoft Excel BI

312/950

llo n

Vincent ISOZ

ch

an

ti

Et enfin si nous voulons récuperer la moyenne globale il semblerait qu'on ne puisse pas encore faire un tel calcul en DAX Query. Il faut créer une mesure DAX et l'ajouter sur un rapport:

É

Et qu'on obtient le même résultat avec:

Microsoft Power BI / Microsoft Excel BI

313/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

314/950

Vincent ISOZ

Exemple 11.: Requête SUMMARIZE avec tables multiples Power BI 2018-03 / Excel 2010 à 2019

ch

an

ti

llo n

Considérons le schéma suivant dans Power BI:

É

Et la requête SUMMARIZE( ) suivante multi-tables:

Microsoft Power BI / Microsoft Excel BI

315/950

llo n

Vincent ISOZ

Nous verrons à la page 328 que nous pouvons arriver au même résultat avec la fonction RELATED( ).

É

ch

an

ti

Considérons le schéma suivant dans Microsoft Excel:

Microsoft Power BI / Microsoft Excel BI

316/950

an

ti

llo n

Vincent ISOZ

É

ch

Avec l'exemple suivant d'une application de SUMMARIZE( ) en monde multi-tables:

Microsoft Power BI / Microsoft Excel BI

317/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

318/950

Vincent ISOZ

Exemple 12.: Requête GROUPBY et CURRENTGROUP Power BI 2018-03 / Excel 2010 à 2019

ch

an

ti

llo n

La requête de type GROUPBY( ) est très similaire à SUMMARIZSE( ) comme l'atteste l'exemple ci-dessous:

É

Outre les performances qui différent (GROUPBY( ) serait plus rapide), un article entier de SQL BI comparant les deux fonctions de requête est disponible ici: https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

Microsoft Power BI / Microsoft Excel BI

319/950

Vincent ISOZ

Exemple 13.: Requête SUMMARIZECOLUMNS Power BI 2018-03 / Excel 2010 à 2019

llo n

Voyons quelque cas d'applications de SUMMARIZECOLUMNS( ):

an

ti

Et:

É

ch

On peut voir qu'à priori cette fonction fait la même chose qu'avec SUMMARIZE( ). Mais la seule différence à laquelle j'ai eu affaire dans la pratique à ce jour, c'est que SUMMARIZECOLUMNS( ) gère des paramètres de filtre comme l'illustre le cas suivante:

Microsoft Power BI / Microsoft Excel BI

320/950

Vincent ISOZ

Exemple 14.: Requête SUMMARIZECOLUMNS et IGNORE Power BI 2019-12 et Excel 2019

llo n

Rappelons ce que nous avons obtenu précédemment:

ti

Cet ensemble de commandes en réalité supprime: 1. Les lignes qui n'ont pas d'articles

an

2. Les articles qui n'ont pas de quantités

É

ch

Pour éviter cela, Microsoft a créé la fonction IGNORE( ) dont voici le résultat:

Microsoft Power BI / Microsoft Excel BI

321/950

Vincent ISOZ

Exemple 15.: Requête TOPN Power BI 2018-03 / Excel 2010 à 2019

ti

Donc voici un premier exemple au plus simple:

llo n

Nous allons voici ici l'aspect de la requête TOPN( ) du point de vue d'une fonction de requête dans un premier temps. Pour voir son aspect comme fonction analytique, nous renvoyons le lecteur à la page 551.

ch

an

Et un deuxième exemple toujours dans Power BI:

Notez que pour extraire par exemple que la troisième valeur, on pourra utiliser dans une mesure la fonction MIN(Table[Total Prix par pièces]), et ensuite en reporter le résultat dans une matrice, tableau ou autre visuel.

É

Ou son équivalent dans Microsoft Excel:

Microsoft Power BI / Microsoft Excel BI

322/950

an

ti

llo n

Vincent ISOZ

É

ch

Il n'existe pas à ce jour et à notre connaissance de méthode en DAX pour trier le résultat!

Microsoft Power BI / Microsoft Excel BI

323/950

Vincent ISOZ

Exemple 16.: Requête RANK.EQ Power BI 2018-03 / Excel 2010 à 2019

É

ch

an

ti

llo n

Pour des raisons pédagogiques nous avons mis cette fonction à la page 570.

Microsoft Power BI / Microsoft Excel BI

324/950

Vincent ISOZ

Exemple 17.: Requête ROW Power BI 2018-03 / Excel 2010 à 2019

an

ti

llo n

La requête ROW( ) renvoie une simple ligne avec une ou plusieurs statistiques à choix. Voyons donc un exemple d'application:

Notez que l'expression suivante:

ch

Ou suivante:

É

Ne marchent pas, car elles sont plutôt réservées à un usage dans le contexte d'un tableau croisé dynamique. Voyons un exemple avec Microsoft Excel utilisant COUNTROWS( ):

Microsoft Power BI / Microsoft Excel BI

325/950

an

ti

llo n

Vincent ISOZ

É

ch

Ou encore un autre exemple utilisant SUM( ), AVERAGE( ) et ROUND( ):

Microsoft Power BI / Microsoft Excel BI

326/950

Vincent ISOZ

É

ch

an

ti

llo n

Nous verrons à la page 370 un cas relativement important d'utilisation de ROW( ) pour les tableaux de bord Power BI.

Microsoft Power BI / Microsoft Excel BI

327/950

Vincent ISOZ

Exemple 18.: Requête SELECTCOLUMNS avec RELATED Power BI 2018-03 / Excel 2010 à 2019

ch

an

ti

llo n

Considérons le schéma suivant dans Power BI:

É

Et une application de SELECTCOLUMNS( ) avec RELATED( ):

Microsoft Power BI / Microsoft Excel BI

328/950

ti

llo n

Vincent ISOZ

É

ch

an

Voyons maintenant un exemple avec Microsoft Excel en considérant le schéma suivant:

Microsoft Power BI / Microsoft Excel BI

329/950

an

ti

llo n

Vincent ISOZ

É

ch

Et enfin un exemple d'application:

Microsoft Power BI / Microsoft Excel BI

330/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

331/950

Vincent ISOZ

Exemple 19.: Requête GENERATE avec RELATEDTABLE Power BI 2018-03 / Excel 2010 à 2019

llo n

La requête GENERATE( ) correspond à une requête interne (inner join).

ch

an

ti

Dans Microsoft Excel, la requête DAX suivante:

É

Renvoie donc toute la table tblVentes avec toutes les lignes de la table tblBonus correspondantes mais seulement les lignes pour lesquelles il y a correspondance et y ajoute les colonnes correspondantes des deux tables c'est pour cela qu'on se retrouve avec 96 lignes et avec deux colonnes en plus:

Microsoft Power BI / Microsoft Excel BI

332/950

ti

llo n

Vincent ISOZ

an

Si on n'utilisait pas RELATEDTABLE( ), on se retrouverait avec un produit cartésien entre les deux tables!!!

É

ch

Conjointement avec CALCULATETABLE( ), nous pouvons ajouter un filtre à GENERATE( ) ou GENERATEALL( ):

Microsoft Power BI / Microsoft Excel BI

333/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

334/950

Vincent ISOZ

É

ch

an

ti

llo n

Avec Power BI c'est moins parlant puisque par défaut nous n'avons pas de colonnes avec des numéros d'identifiants de lignes. Mais voici toutefois la requête équivalente:

Microsoft Power BI / Microsoft Excel BI

335/950

Vincent ISOZ

Exemple 20.: Requête GENERATEALL avec RELATEDTABLE Power BI 2018-03 / Excel 2010 à 2019

llo n

La requête GENERATEALL( ) correspond à une requête interne (full join).

ch

an

ti

Dans Microsoft Excel, la requête DAX suivante:

É

Renvoie donc toute la table tblVentes avec toutes les lignes de la table tblBonus correspondant, c'est pour cela qu'on se retrouve avec 112 lignes:

Microsoft Power BI / Microsoft Excel BI

336/950

an

ti

llo n

Vincent ISOZ

Si on n'utilisait pas RELATEDTABLE( ), on se retrouverait avec un produit cartésien entre les deux tables!!!

É

ch

À nouveau, avec Power BI c'est moins parlant puisque par défaut nous n'avons pas de colonnes avec des numéros d'identifiants de lignes. Mais voici toutefois la requête équivalente:

Microsoft Power BI / Microsoft Excel BI

337/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

338/950

Vincent ISOZ

Exemple 21.: Requête NATURALINNERJOIN Power BI 2018-03 / Excel 2010 à 2019

É

ch

an

ti

llo n

Pour la fonction NATURALINNERJOIN( ), l'excellent rappel suivant de Curbal peut être utile:

Figure 8 Types de jointures DAX (source: Curbal)

Considérons notre schéma habituel:

Microsoft Power BI / Microsoft Excel BI

339/950

an

ti

llo n

Vincent ISOZ

É

ch

Et notez que la TablePrincipale a à l'origine 111 lignes et observez bien les colonnes visibles à gauche de la table comme on peut le voir ci-dessous:

Microsoft Power BI / Microsoft Excel BI

340/950

ch

an

ti

llo n

Vincent ISOZ

É

Et créons la table suivante de jointure naturelle et notez le nombre de lignes et les colonnes rajoutées!:

Microsoft Power BI / Microsoft Excel BI

341/950

É

ch

an

ti

llo n

Vincent ISOZ

Donc la jointure naturelle fait bien son travail! Par ailleurs n'oubliez pas que ce type de jointure est symétrique, donc peu importe l'ordre dans lequel vous mettez les tables dans les arguments de NATURALINNERJOIN( ). Microsoft Power BI / Microsoft Excel BI

342/950

Vincent ISOZ

llo n

Voyons un autre cas intéressant! Considérons le TOPN( ) suivant:

É

ch

an

ti

Si on veut rapatrier toutes les lignes correspondant à ces trois secteurs, il nous suffira d'écrire:

Microsoft Power BI / Microsoft Excel BI

343/950

Vincent ISOZ

Exemple 22.: Requête NATURALLEFTOUTERJOIN Power BI 2018-03 / Excel 2010 à 2019

É

ch

an

ti

llo n

Pour la fonction NATURALLEFTOUTERJOIN( ), l'excellent rappel suivant de Curbal peut être utile (notez que contrairement à NATURALINNERJOIN( ), cette fonction n'est pas "symétrique"):

Figure 9 Types de jointures DAX (source: Curbal)

Considérons notre schéma habituel:

Microsoft Power BI / Microsoft Excel BI

344/950

an

ti

llo n

Vincent ISOZ

É

ch

Et notez que la TablePrincipale a à l'origine 111 lignes et observez bien les colonnes visibles à gauche de la table comme on peut le voir ci-dessous:

Microsoft Power BI / Microsoft Excel BI

345/950

ch

an

ti

llo n

Vincent ISOZ

É

Et créons la table suivante de jointure naturelle externe gauche et notez le nombre de lignes et les colonnes rajoutées!:

Microsoft Power BI / Microsoft Excel BI

346/950

É

ch

an

ti

llo n

Vincent ISOZ

Ou inversement: Microsoft Power BI / Microsoft Excel BI

347/950

É

ch

an

ti

llo n

Vincent ISOZ

Donc la jointure naturelle fait bien son travail!

Microsoft Power BI / Microsoft Excel BI

348/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

349/950

Vincent ISOZ

Exemple 23.: Requête ADDCOLUMNS Power BI 2018-03 / Excel 2010 à 2019

an

ti

Voyons donc d'abord un cas simple:

llo n

La requête ADDCOLUMNS( ) permet de rajouter des colonnes simples ou composées de calculs dans une sous-requête. Mais comme nous le verrons à la page , ce n'est de loin pas sa seule utilité!

É

ch

Ou l'équivalent dans Microsoft Excel:

Microsoft Power BI / Microsoft Excel BI

350/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

351/950

Vincent ISOZ

Exemple 24.: Requête ROLLUP Excel BI 2019 / Power BI 2019-10

llo n

La fonction ROLLUP( ) est vraiment une fonction de requête DAX pure dédiée typiquement à SSAS ou au reverse DAX Microsoft Excel. Elle ne fait pas vraiment de sens dans Power BI puisque nous y avons les tableaux croisés dynamiques (mais elle existe toutefois dans ce dernier!!!!).

É

ch

an

ti

Voyons donc un exemple d'application dans Microsoft Excel (notez que les sous-totaux se trouvent au-dessus des groupes!):

Microsoft Power BI / Microsoft Excel BI

352/950

Vincent ISOZ

Exemple 25.: Requête ROLLUPGROUP Excel BI 2019 / Power BI 2019-10

llo n

La fonction ROLLUPGRUOP( ) est vraiment une fonction de requête DAX pure dédiée typiquement à SSAS ou au reverse DAX Microsoft Excel. Elle ne fait pas vraiment de sens dans Power BI puisque nous y avons les tableaux croisés dynamiques (mais elle existe toutefois dans ce dernier!!!!).

ch

an

ti

Voyons donc un exemple d'application dans Microsoft Excel (son utilisation est loin d'être triviale):

É

Comparons le ROLLUP tout seul fait avant avec le ROLLUPGROUP fait just à l'instant côte à côté (cela peut aider pour la compréhension):

Microsoft Power BI / Microsoft Excel BI

353/950

Vincent ISOZ

Avec ROLLUPGROUP

É

ch

an

ti

llo n

ROLLUP seul

Microsoft Power BI / Microsoft Excel BI

354/950

Vincent ISOZ

Exemple 26.: Requête ISSUBTOTAL Excel BI 2019 / Power BI 2019-10

llo n

La fonction ROLLUPADDISSUBOTTAL( ) est encore vraiment une fonction de requête DAX pure dédiée typiquement à SSAS ou au reverse DAX Microsoft Excel. Elle ne fait pas vraiment de sens dans Power BI puisque nous y avons les tableaux croisés dynamiques (mais elle existe toutefois dans ce dernier!!!!). Voyons deux exemples d'applications dans Microsoft Excel!

É

ch

an

ti

D'abord avec ROLLUP( ) seul:

Et enfin avec ROLLUP( ) et ROLLUPGROUP( ):

Microsoft Power BI / Microsoft Excel BI

355/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

356/950

Vincent ISOZ

Exemple 27.: Requête ROLLUPADDISSUBTOTAL Excel BI 2019 / Power BI 2019-10

llo n

La fonction ROLLUPADDISSUBOTTAL( ) est encore vraiment une fonction de requête DAX pure dédiée typiquement à SSAS ou au reverse DAX Microsoft Excel. Elle ne fait pas vraiment de sens dans Power BI puisque nous y avons les tableaux croisés dynamiques (mais elle existe toutefois dans ce dernier!!!!).

É

ch

an

ti

Voyons donc un exemple d'application dans Microsoft Excel:

Microsoft Power BI / Microsoft Excel BI

357/950

Vincent ISOZ

Data Definition (DAX) Au même titre que le domaine DDL (Data Definition Language) du SQL, le DAX à son propre DDL qu'il faut normalement apprendre avant de faire de l'analyse DAX! Le lecteur doit cependant garder à l'esprit que: Toutes les tables créées en DAX peuvent aussi l'être avec Power Query (en M) à notre connaissance

•

Les tables créées en DAX ne sont pas disponibles par la suite dans Power Query (alors que les tables Power Query sont toujours disponibles dans le Data Model)

É

ch

an

ti

llo n

•

Microsoft Power BI / Microsoft Excel BI

358/950

Vincent ISOZ

Exercice 28.: Commande CALENDAR Power BI 2018-03

llo n

Nous savons que pour utiliser de nombreuses fonctions DAX il est obligé d'avoir une table de calendrier.

Nous voulons créer une table calendrier nommée tbl_Calendrier qui va de 1990-01-01 jusqu'au 2005-01-01.

É

ch

an

ti

Cet exercice se poursuit dans sa logique à la page 637 et ensuite à la page 690.

Microsoft Power BI / Microsoft Excel BI

359/950

Vincent ISOZ

Solution

llo n

On clique sur Modeling/New Table:

ti

On saisit la relation suivante:

an

Et on valide par entrée pour obtenir:

É

ch

Et si on va visualiser la table, nous avons bien:

Microsoft Power BI / Microsoft Excel BI

360/950

llo n

Vincent ISOZ

ti

Sinon voici un calendrier relativement complet (en une seule langue cependant…):

É

ch

an

Calendrier = ADDCOLUMNS ( CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ), "Date As Integer", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Month number", FORMAT ( [Date], "MM" ), "Year Month number", FORMAT ( [Date], "YYYY/MM" ), "Semester", "S" & IF ( MONTH ( [Date] ) <= 6, 1, 2 ), "Year Month Short", FORMAT ( [Date], "YYYY/mmm" ), "Month Name Short", FORMAT ( [Date], "mmm" ), "Month Name Long", FORMAT ( [Date], "mmmm" ), "Day of Month Number", DAY ( [Date] ), "Number of Days in Month", DAY ( EOMONTH ( [Date], 0 ) ), "Day Of Week Number", WEEKDAY ( [Date], 2 ), "Day Of Week", FORMAT ( [Date], "dddd" ), "Day Of Week Short", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "Week Number", "Week" & WEEKNUM ( [Date], 2 ), "Week", WEEKNUM ( [Date], 2 ), "ISO 8601 Week", WEEKNUM ( [Date], 21 ), "Year Quarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) ) Nous reviendrons plus tard sur toutes ces fonctions! Microsoft Power BI / Microsoft Excel BI

361/950

Vincent ISOZ

É

ch

an

ti

llo n

Attention à ne pas confondre EOMONTH( ) qui est une fonction de date classique avec la fonction ENDOFMONTH( ) qui est une fonction d'intelligence temporelle que nous verrons plus loin!

Microsoft Power BI / Microsoft Excel BI

362/950

Vincent ISOZ

Exercice 29.: Commande CALENDARAUTO Power BI 2018-03

llo n

Nous savons que pour utiliser de nombreuses fonctions DAX il est obligé d'avoir une table de calendrier.

É

ch

an

ti

Nous voulons créer une table calendrier nommée tbl_CalendrierAuto qui se crée automatiquement avec une plage de dates qui dépend de tous les champs de dates contenus dans le modèle Power BI.

Microsoft Power BI / Microsoft Excel BI

363/950

Vincent ISOZ

Solution

llo n

On clique sur Modeling/New Table:

ti

On saisit la relation suivante:

an

Et on valide par entrée pour obtenir:

É

ch

Et si on va visualiser la table, nous avons bien:

Microsoft Power BI / Microsoft Excel BI

364/950

Vincent ISOZ

Cette fonction a un paramètre qui permet de décaler l'année fiscale à un mois donné de l'année. Par exemple: Après (décalage de 1 mois):

É

ch

an

ti

llo n

Avant:

Microsoft Power BI / Microsoft Excel BI

365/950

Vincent ISOZ

Exercice 30.: Commande SUMMARIZE Power BI 2018-03

É

ch

an

ti

llo n

Créez une table qui agrège les noms des articles, les sommes des quantités (attention de prendre la colonne Quantité_Clean comme le suggère l'exemple à l'exercice de la page 408).

Microsoft Power BI / Microsoft Excel BI

366/950

Vincent ISOZ

Solution

llo n

On clique sur Modeling/New Table:

É

ch

an

ti

Et on y met:

Microsoft Power BI / Microsoft Excel BI

367/950

Vincent ISOZ

Exercice 31.: Commande ADDCOLUMNS Power BI 2018-03

llo n

Un premier exercice illustrant cette commande dans un cas fameux est disponible à la page 690. Sinon en ajoutant un IF (voir exercices à partir de la page 408), ajoutez une nouvelle colonne de type image pour des KPI basés sur Quantité_Clean avec les critères de votre choix. Sachant que les images de KPI sont disponibles aux adresses suivantes:

http://www.sciences.ch/tmp/kpi/powerbi_kpi_arrow_down_red.jpg

•

http://www.sciences.ch/tmp/kpi/powerbi_kpi_arrow_middle_orange.jpg

•

http://www.sciences.ch/tmp/kpi/powerbi_kpi_arrow_up_green.jpg

É

ch

an

ti

•

Microsoft Power BI / Microsoft Excel BI

368/950

Vincent ISOZ

Solution

É

ch

an

Ce qui donnera:

ti

llo n

En reprenant la suite de l'exercice de la page 366 (sur SUMMARIZE) on écrit:

Microsoft Power BI / Microsoft Excel BI

369/950

Vincent ISOZ

Exercice 32.: Commande UNION Power BI 2018-03

llo n

Basiquement comme son nom l'indique, UNIONS fusionne simplement des tables mais sans en supprimer les doublons (sinon quoi faudra l'encapsuler dans la fonction DISTINCT( )).

an

ti

Pour l'illustrer, considérons les trois tables suivantes importées:

É

ch

Comment les fusionner ?

Microsoft Power BI / Microsoft Excel BI

370/950

Vincent ISOZ

Solution

ti

llo n

Eh bien la solution consiste simplement à écrire:

É

ch

an

Et au cas où il y aurait des doublons, nous pouvons y associer DISTINCT( ) que nous connaissons déjà bien:

Microsoft Power BI / Microsoft Excel BI

371/950

Vincent ISOZ

Exercice 33.: Commande UNION et ROW Power BI 2018-03

É

ch

an

ti

llo n

L'idée ici est d'utiliser les commandes de requêtes UNION( ) et ROW( ) pour créer la table statique suivante dans Power BI (on pourrait aussi bien évidemment faire un copier/coller spécial d'une table Microsoft Excel à une nouvelle table statique…):

Microsoft Power BI / Microsoft Excel BI

372/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution non triviale est donnée par:

Microsoft Power BI / Microsoft Excel BI

373/950

Vincent ISOZ

Exercice 34.: Commande UNION et GENERATESERIES Power BI 2018-03

É

ch

an

ti

llo n

Dans le domaine des statistiques nous avons souvent besoin de créer une table des centiles. Cette dernière peut être régulière ou irrégulière. L'exercice ici est de construire une table du type suivant à l'aide de la fonction UNION( ) et GENERATESERIES( ):

Microsoft Power BI / Microsoft Excel BI

374/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

375/950

Vincent ISOZ

Solution

ch

an

ti

llo n

La solution est donnée par la création de table suivante :GENERATESERIES(0,20,1)

É

Évidemment on aurait pu aussi créer une table depuis un unique GENERATSERIES( ) !

Microsoft Power BI / Microsoft Excel BI

376/950

Vincent ISOZ

Exercice 35.: Commande DATATABLE Power BI 2018-03

É

ch

an

ti

llo n

L'idée ici est d'utiliser la commande de requête DATATABLE( ) (historiquement elle plus récente que UNION( ) et ROW( )!) pour créer la table statique suivante dans Power BI (on pourrait aussi bien évidemment faire un copier/coller spécial d'une table Microsoft Excel à une nouvelle table statique…):

Microsoft Power BI / Microsoft Excel BI

377/950

Vincent ISOZ

Solution

an

ti

llo n

La solution non triviale est donnée par:

É

ch

Notez à ce jour le problème majeur suivant (surtout pour les utilisateurs de SalesForce) et qui n'a pas encore de solution simple en 2019:

Nous voyons donc bien que Power BI et DAX n'est pas Microsoft Power BI / Microsoft Excel BI

378/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

379/950

Vincent ISOZ

Exercice 36.: Commande INTERSECTION Power BI 2018-03

an

ti

llo n

Pour qui connaissant le SQL ou la théorie des ensemble, l'intersection est un grand classique! Pour voir un exemple, considérons les trois tables suivantes:

É

ch

Nous souhaiterions fusionner par intersection seulement la colonne strNbArticle des trois tables?

Microsoft Power BI / Microsoft Excel BI

380/950

Vincent ISOZ

Solution

llo n

Pour la solution le lecteur doit savoir que (curieusement) la fonction INTERSECTION( ) ne prend pas en compte plus de deux arguments à ce jour. Donc la solution sera donnée par:

Ce qui est bien conforme!

É

ch

an

ti

Nous verrons cependant plus loin que certaines personnes utilisent INTERSECTION( ) pour créer des relations virtuelles!

Microsoft Power BI / Microsoft Excel BI

381/950

Vincent ISOZ

Exemple 37.: Commande CROSSJOIN Power BI 2018-03 / Excel 2010

É

ch

an

ti

Voyons un exemple avec Power BI:

llo n

Nous allons voici ici un cas connu en statistique qui est la création d'une table résultant du produit cartésien de deux colonnes à l'aide de la fonction CROSSJOIN( ).

Ou lorsqu'on a des valeurs vides, nous écrirons:

Microsoft Power BI / Microsoft Excel BI

382/950

É

ch

an

ti

llo n

Vincent ISOZ

Ou en considérant le schéma suivant dans Microsoft Excel:

Microsoft Power BI / Microsoft Excel BI

383/950

an

ti

llo n

Vincent ISOZ

É

ch

Nous pouvons faire le produit cartésien de toutes les ventes avec tous les bonus (ie remises):

Microsoft Power BI / Microsoft Excel BI

384/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

385/950

Vincent ISOZ

Exemple 38.: Commande GENERATESERIES avec génération de de séquences ou nombres aléatoires

llo n

Power BI 2018-03 / Excel 2016

an

ti

Comment générer en DAX un simple table de rangs (utiles pour les filtres de rangs):

É

ch

Et pour ceux qui ont des listes de produits alphabétiques, il est utile d'avoir un index des lettres de l'alphabet. Pour cela on écrira:

Microsoft Power BI / Microsoft Excel BI

386/950

ch

an

ti

llo n

Vincent ISOZ

Voyons maintenant un cas très important en finance et statistique industrielle. La génération de nombres aléatoires.

É

Celle-ci peut se faire aisément en conjonction avec GENERATESERIES( ) comme l'illustre la capture d'écran ci-dessous:

Microsoft Power BI / Microsoft Excel BI

387/950

an

ti

llo n

Vincent ISOZ

Ou en même temps, nous voyons un exemple d'application de RAND( ), RANDBETWEEN( ) et NORM.S.INV( ). Nous pourrions évidemment ajouter NORM.INV( ) à la liste sans aucune difficulté!

É

ch

Remarque: Visiblement, il y a un "seed", et c'est la même chose pour les fonctions qui génèrent des nombres aléatoires en DAX sur des colonnes!

Microsoft Power BI / Microsoft Excel BI

388/950

Vincent ISOZ

Data Analysis (DAX) Avant de commencer ce chapitre sachez qu'un pourcentage important de consultants, experts informatiques ou même Data Scientists peinent avec la logique et la jungle de DAX. C'est pour cela que vous trouverez sur Internet un certain nombre d'articles sur les "haters" de DAX.

llo n

En tant qu'auteur de ce document, je ne souhaite pas prendre parti, mais sachez que DAX peut comme tout autre domaine relativement complexe être résumé par l'illustration ci-dessous:

La maîtrise de DAX nécessite

De La CONNAISSANCE,

an

De la PATIENCE,

ti

Du TEMPS,

ch

Et surtout de l'EXPÉRIENCE

É

J'ai remarqué que pour une grande majorité d'individus, un minimum de 5 jours de formation était nécessaire pour qu'ils puissent commencer appréhender les finesses de ce langage mais aussi ses dangers et faiblesses. Ceci étant dit… commençons!

Microsoft Power BI / Microsoft Excel BI

389/950

Vincent ISOZ

On considère cinq familles d'applications de DAX: 1. Les requêtes DAX qui créent des tables 2. Les formules DAX qui créent des colonnes dans les tables originales (DAX calculated columns)

llo n

3. Les mesures DAX dites "DAX query context"

3.1. S'appliquant à un sous-ensemble des sources

3.2. Basées sur les en-têtes de lignes et colonnes des TCS, segments et filtres du rapport

ti

3.3. Différents pour chaque cellule d'un TCD

an

4. Les mesures DAX de type query context avec filtres dites "DAX filter context"

É

ch

4.1. Elles sont identifiées car elles contiennent des paramétrages de filtres dans la formule

5. Les mesures DAX qui s'exécutent sur les lignes des tables sources et dites "DAX rows context". On les reconnaît car elles impliquent des fonctions qui finissent par la lettre X: Microsoft Power BI / Microsoft Excel BI

390/950

Vincent ISOZ

llo n

Fonctions AGGREGATEX SUMX AVERAGEX MINX MAXX COUNTX COUNTAX

ch

an

ti

Pour ceux qui sont intéressés à avoir en local un rapide résumé de toutes les fonctions, il y a un excellent fichier Power BI qu'on peut télécharger gratuitement sur le Web:

É

Nous vous le recommandons fortement!

Microsoft Power BI / Microsoft Excel BI

391/950

Vincent ISOZ

Une question très fréquemment posée est DAX ou M (Power Query) lequel est le plus rapide ou conseillé? La réponse est: cela dépend! Effectivement: On privilégiera Power Query et M pour sa simplicité et donc compréhensible et modifiable par la majorité des collaborateurs.

•

On privilégiera Power Query si l'utilisateur final est un Data Steward

•

Sinon on privilégiera le DAX pour la rapidité et l'espace mémoire (quand cela est possible d'appliquer le DAX car pour certaines sources c'est problématique voire simplement pas possible à ce jour)

•

On privilégiera le DAX si l'utilisateur final est un Data Scientist

•

Pour les fonctions de type "Intelligence Temporelle" on privilégiera naturellement DAX qui a des fonctions faites pour!

É

ch

an

ti

llo n

•

Microsoft Power BI / Microsoft Excel BI

392/950

Vincent ISOZ

Compatibilités fonctions DAX

Description Introduit avec Excel 2016 Introduit avec Excel 2016 Septembre 2019 Septembre 2019 Mai 2019 Avril 2019 Mars 2019 Mars 2019 Mars 2019 Décembre 2019 Août 2018 Août 2018 Août 2018 Août 2018 Août 2018 Août 2018 Août 2018 Août 2018 Août 2018 April 2018 Mars 2018 Mars 2018 Mars 2018

ch

an

ti

Fonction MEDIAN MEDIANX CONVERTE REMOVEFILTERS SELECTEDMEASUREFORMATSTRING ALLCROSSFILTERED CONTAINSSTRING CONTAINSSTRINGEXACT DISTINCTCOUNTNOBLANK APPROXIMATEDISTINCTCOUNT ISINSCOPE NORM.DIST NORM.INV NORM.S.INV T.DIST T.DIST.2T T.DIST.RT T.INV T.INV.2T DISTINCT COMBINEVALUES UTCNOW UTCTODAY

llo n

Microsoft rajoute régulièrement de nouvelles fonctions DAX. Ce qui n'est pas sans poser de problèmes (au même titre qu'avec Microsoft Excel) de compréhension du DAX. J'ai commencé ci-dessous à construire une liste des problèmes rencontrés jusqu'à maintenant vu que Microsoft s'amuse à en supprimer la liste à chaque nouvelle version de la suite Microsoft Office (sans commentaires…!):

Encore une fois!!!! Gardez bien en tête les trois points suivants quand vous travaillez avec du DAX:

É

1. Certaines fonctions semblent faire doublon mais en réalité ce sont des évolutions historiques (pensez à LOOKUP, VLOOKUP et XLOOKUP dans Excel par exemple!) 2. Certaines fonctions remplacent d'autres au niveau de leur comportement uniquement pour améliorer la performance (l'exemple le plus flagrant est REMOVEFILTERS qui a remplacé ALL en septembre 2019) 3. Certaines fonctions ne peuvent être réalisées (écrites) que si dès le début la bonne table est sélectionnée pour créer la mesure!

Microsoft Power BI / Microsoft Excel BI

393/950

Vincent ISOZ

Raccourcis claviers DAX Ces raccourcis ne marchent pas à ce jour dans Power Pivot (pour Microsoft Excel): Indentation (si curseur en début de ligne): TAB

•

Suppression Indentation (si curseur en début de ligne): ALT+TAB

•

Indentation: Ctrl + ]

•

Suppression Indentation: Ctrl + [

•

Nouvelle ligne en conservant le retrait: MAJ + ENTREE

•

Nouvelle ligne à partir de la première ligne: ALT + ENTREE

•

Activer Intelligence: CTRL + ESPACE

•

Commenter plusieurs lignes: CTRL + KC ou CTRL + /

•

Décommenter plusieurs lignes: CTRL + KU ou CTRL + /

•

Déplacer la ligne actuelle vers le haut / bas: ALT + Flèche haut / bas

•

Entrez plusieurs lignes de code à la fois: CTRL + ALT + Flèche haut / bas

•

Aller au numéro de ligne: CTRL + G

•

Supprimer un mot: CTRL + DEL

•

Suppression d'une / plusieurs lignes: CTRL + MAJ + K. Pour mettre en surbrillance plusieurs lignes, puis CTRL + MAJ + K

ch

an

ti

llo n

•

Trouver et remplacer un mot: CTRL + D pour mettre en surbrillance le mot en cours, CTRL + D à nouveau pour trouver / highlighter le même mot suivant. Continuez à appuyer sur CTRL + D pour trouver / mettre en évidence tous les mêmes mots, puis commencez à taper pour remplacer tous les mots à la fois

•

Trouvez et remplacez tout d'un genre à la fois: CTRL + MAJ + L pour mettre en évidence une partie de votre expression DAX puis commencez à taper pour remplacer les mots surlignés à la fois

É

•

Microsoft Power BI / Microsoft Excel BI

394/950

Vincent ISOZ

Commentaires DAX DAX prend en charge deux types de commentaires différents: Les commentaires sur une seule ligne peuvent être démarrés avec //

•

Vous pouvez commenter plusieurs lignes ou commenter un texte au milieu d'une ligne en utilisant / * * / (par exemple = 1 + / * un commentaire * / 2)

llo n

•

an

ti

Cela marche seulement dans SQL Server, Power BI et les requêtes DAX dans Microsoft Excel. Pas encore dans les formules DAX de Microsoft Excel (du moins pas à ce jour!).

ch

Figure 10 Requête DAX dans Microsoft Excel avec commentaires

É

Ou avec Power BI:

Figure 11 Requête DAX dans Power BI avec commentaires

Microsoft Power BI / Microsoft Excel BI

395/950

Vincent ISOZ

Icônes DAX Il faut parfois être bien concentré avec Power BI ou Microsoft Excel pour comprendre à quel type de DAX nous avons affaire. Voici quelques symboles qu'il faut bien comprendre: Description Une simple table (importée)

llo n

Logo

Une table DAX (ie requête DAX)

Un champ de type Data (donc on pourra extraire la hiérarchie de dates!) Un champ numérique qui pourra être utilisée pour faire des agrégations Un mesure DAX

ti

Un colonne DAX

Un mesure DAX de type "paramètre" (What-IF)

an

Une hiérarchie DAX

Un dossier de mesures DAX

Une table uniquement de mesures DAX

Une erreur dans un requête ou mesure DAX

Tableau 2 Tableau icônes DAX

É

ch

Groupe

Microsoft Power BI / Microsoft Excel BI

396/950

Vincent ISOZ

É

ch

an

ti

llo n

Organiser les fonctions

Microsoft Power BI / Microsoft Excel BI

397/950

Vincent ISOZ

"Table" de mesures

an

ti

llo n

Concernant l'icône suivante: . Pour l'obtenir c'est assez subtile (attention cette méthode n'est pas recommandée si vous utilisez le Q&A)! D'abord créez une table manuelle avec typiquement:

Vous validez pour obtenir:

ch

Ensuite vous y mettez au moins une mesure:

É

Ensuite vous masquez la colonne Formules:

Microsoft Power BI / Microsoft Excel BI

398/950

llo n

Vincent ISOZ

ti

Ce qui donnera:

É

ch

an

Ensuite il faut fermer et rouvrir le fichier (!!!), ce qui donnera:

Microsoft Power BI / Microsoft Excel BI

399/950

Vincent ISOZ

É

ch

an

ti

llo n

Dossiers et sous-dossiers de mesures

Microsoft Power BI / Microsoft Excel BI

400/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

401/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

402/950

Vincent ISOZ

Opérateurs DAX En DAX (mais pas seulement…) de nombreux opérateurs peuvent être utilisés pour différents fonctions. En voici un résumé:

Logique

&&

Comparaison

an

||

Signification Addition Soustraction Multiplication Division Exponentiation Égalité Plus que grand que Plus petit que Plus grand ou égale à Plut petit ou égal à Différent de Concatène deux valeurs de textes Opérateur ET entre deux expressions booléennes Opérateur OU entre deux expressions booléennes Appartenance à une liste Négation

IN

NOT

Exemple 2+3 2–3 2*3 3/2 2^3 2=3 2>3 2<3 2 >= 3 2 <= 3 2<>3 "2" & "3"

Résultat 5 -1 6 1.5 8 FALSE FALSE TRUE FALSE TRUE TRUE 23

(2 = 3) && (1 = 1)

FALSE

(2 = 3) || (1 = 1)

TRUE

2 IN {1, 2, 3}

TRUE

NOT 2 = 3

TRUE

llo n

Concaténation

Opérateur + * / ^ = > < >= <= <> &

ti

Type Arithmétique

ch

Tableau 3Tableau opérateurs DAX

É

Notez donc que DAX ne gère donc à ce jour ni les symboles de Wildcards, ni les RegEx!

Microsoft Power BI / Microsoft Excel BI

403/950

Vincent ISOZ

DAX Catégorie Fonctions d'Informations Exercice 1.: Fonction USERNAME

llo n

Power BI 2019-10

Voici une fonction d'information qui est probablement la plus utilisée à travers le monde pour la RLS (Row Level Security).

an

ti

Nous ne la présentons pas donc sous forme d'un exercice:

É

ch

Notez qu'une fois publié sur Power BI services, cette fonctione donne:

D'après la documentation cette fonction devrait donner "domaine/nom_utilisateur". Mais c'est le cas en réalité uniquement pour Power BI Report Server! Si on possède Power BI Services, il n'y aura pas de différence entre la fonction USERNAME( ) et la fonction USERPRINCIPALNAME( ) que nous allons voir ci-dessous!

Microsoft Power BI / Microsoft Excel BI

404/950

Vincent ISOZ

Exercice 2.: Fonction USERPRINCIPALNAME Power BI 2019-10

llo n

USERPRINCIPALNAME( ) doit donc forcer l'affichager du nom de l'utilisateur sous forme d'adresse e-mail, même si on utilise Power BI Report Server.

an

ti

Nous ne la présentons pas donc sous forme d'un exercice:

É

ch

Notez qu'une fois publié sur Power BI services, cette fonctione donne:

Microsoft Power BI / Microsoft Excel BI

405/950

Vincent ISOZ

Exercice 3.: Fonction USERCULTURE Power BI 2019-10

llo n

La fonction USERCULTURE est très utilisée dans les multinationales où les rapports doivent être multi-langue. Cette fonction renvoie le code de lanque qui pourra plus tard être utilisé à l'aide de fonctions DAX que l'on verra plus tard, pour changer automatiquement les phrases sur les rapports, les légendes et les titres des graphiques.

ch

an

ti

On crée un mesure utilisant cette fonction et on observe le résultat:

É

Notez qu'à ce jour, cette fonction n'est pas reconnue comme fonction officielle (raison pour laquelle elle n'apparaît pas dans la liste des fonctions et qu'elle est soulignée en rouge!).

Microsoft Power BI / Microsoft Excel BI

406/950

Vincent ISOZ

DAX Variables Power BI 2019-10

llo n

Ici pas d'exercice, juste voir qu'on peut utiliser des variables en DAX (sauf erreur de ma part c'est depuis 2017 que cela existe). Voyons un cas simple car on retrouvera les variables DAX relativement souvent dans la pratique (car permettant principalement d'écrire de longues formules plus élégamment mais pas que…!!!), et surtout dans les Quick Measures DAX (voir page 709).

É

ch

an

ti

L'exemple le plus simple (mais pas le plus convaincant) est de reproduire la Quick Measure que l'on verra à la page 802:

Microsoft Power BI / Microsoft Excel BI

407/950

Vincent ISOZ

DAX Catégorie Fonctions Logiques Exercice 4.: Fonction IF (+VALUE)

llo n

Power BI 2018-03

É

ch

an

ti

Pour la suite nous nous interdisons d'aller dans PowerQuery pour nettoyer le typage de certaines données. Or la colonne Quantité contient des "NC". Trouvez en utilisant une nouvelle colonne qui sera nommée Quantité_Clean et les fonctions IF et VALUE comment faire ceci.

Microsoft Power BI / Microsoft Excel BI

408/950

Vincent ISOZ

Solution

É

ch

Et on y met:

an

ti

llo n

Dans le volet de droite on cliquer sur New Column:

Microsoft Power BI / Microsoft Excel BI

409/950

Vincent ISOZ

Exercice 5.: Fonction IF, OR (+VALUE) Power BI 2018-03

É

ch

an

ti

llo n

Après avoir fait les exercices à la page 359 (création d'un calendrier) et page 690 (ajout de colonnes complémentaires dans le calendrier), rajoutez à l'aide des fonctions IF et OR une colonne avec les numéros de semestre.

Microsoft Power BI / Microsoft Excel BI

410/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Vous rajoutez à la fin de la formule DAX simplement la partie suivante:

Microsoft Power BI / Microsoft Excel BI

411/950

Vincent ISOZ

Exercice 6.: Fonction AND Power BI 2018-03

É

ch

an

ti

llo n

Pour un exemple concret et intéressant de la fonction AND le lecteur pourra se report à l'exercice sur SUMX( ) à la page 493 ou avec COUNTROWS( ) à la page 545.

Microsoft Power BI / Microsoft Excel BI

412/950

Vincent ISOZ

Exercice 7.: Fonction SWITCH (pour KPI) Power BI 2018-03

an

ti

llo n

Considérant ce que nous avions fait à la page 368 sur la création de KPI:

É

ch

Changez la formule DAX correspondant de la colonne KPI pour utiliser un SWITCH( ) plutôt qu'un IF( )!

Microsoft Power BI / Microsoft Excel BI

413/950

Vincent ISOZ

Solution

ti

llo n

L'idée est simplement de changer cela (voir page 368):

É

ch

an

En cela:

Microsoft Power BI / Microsoft Excel BI

414/950

Vincent ISOZ

DAX Catégorie de Filtres Exercice 8.: Fonction RELATED

llo n

Power BI 2018-03

RELATED( ) est probablement la fonction la plus connue du monde DAX puisqu'elle permet d'exploiter l'opportunité qu'offrent les relations entre tables.

É

ch

an

ti

Pour voir un exemple de cette fonction, préparez d'abord le schéma suivant :

où SourceRemisesClients (avant renommage en csv_SourceRemisesClients) contient:

Microsoft Power BI / Microsoft Excel BI

415/950

É

ch

an

ti

llo n

Vincent ISOZ

L'objectif est de créer dans un dashboard un tableau qui contient par article le total net + "l'escompte" qui est indiquée ci-dessus dans la colonne Bonus.

Microsoft Power BI / Microsoft Excel BI

416/950

Vincent ISOZ

Solution

ti

llo n

Le début de la formule est classique et utilise:

É

ch

an

La suite est de l'arithmétique pure (il s'agit toujours de la même colonne mais qu'on renomme au fur et à mesure de notre progression):

et finalement on faire intervenir RELATED pour aller rapatrier le "bonus" de chacun des clients:

Microsoft Power BI / Microsoft Excel BI

417/950

llo n

Vincent ISOZ

É

ch

an

ti

Et rappelez-vous que tout du long, nous sommes dans une formule DAX de colonne et non pas de mesure!!!!

Microsoft Power BI / Microsoft Excel BI

418/950

Vincent ISOZ

Exercice 9.: Fonction CALCULATE Power BI 2018-03

É

ch

an

ti

llo n

CALCULATE( ) permet d'appliquer une opération à une colonne selon un ou plusieurs filtres. Elle prend au moins deux arguments, le premier étant l'opération à réaliser et le second une expression booléenne servant de filtre. On peut ainsi, y mettre plusieurs filtres séparés par des points virgules…

Microsoft Power BI / Microsoft Excel BI

419/950

Vincent ISOZ

Solution

ti

llo n

Nous allons déterminer la part du chiffre d'affaires nette (donc déduction faite du rabais) réellement encaissé en 2000, ce qui implique une double condition de filtrage (sur les dates de commandes 2000 et factures payées « OUI », cet exercice a déjà été réalisé avec SUMX et FILTER et AND plus haut donc on devrait obtenir le même résultat :

É

ch

an

Comme on peut le voir on a obtenu le même résultat qu'avec CA _net_Encaissé _2000 :

Microsoft Power BI / Microsoft Excel BI

420/950

Vincent ISOZ

Exercice 10.: Fonctions FILTER et AND dans CALCULATE Power BI 2018-03

llo n

C'est la fonction de filtrage par excellence pour cela elle ne fonctionne pas seule mais avec une fonction de calcule idéalement statistique. Elle prend en argument d'abord une table, puis une expression booléenne ensuite. Cette expression booléenne peut tirer avantage de AND et OU dans le cas où l'on souhaite combiner plusieurs conditions.

É

ch

an

ti

Nous souhaitons dans un tableau afficher par Pays la Moyenne des ventes pour l'année 2000 et le secteur des Assurances.

Microsoft Power BI / Microsoft Excel BI

421/950

Vincent ISOZ

Solution

llo n

Étant donné qu'il existe plusieurs exercices combinant FILTER( ) et des fonctions statistiques, nous allons présenter cette fois un cas de combinaison entre FILTER( ) et CALCULATE( ) :

É

ch

an

ti

La mesure calculée ci-dessous est la moyenne des ventes réalisées en 2000 dans le secteur des Assurances uniquement :

Microsoft Power BI / Microsoft Excel BI

422/950

Vincent ISOZ

Exercice 11.: Fonctions FILTER et IN dans CALCULATE Power BI 2018-03

ch

an

ti

En reprenant l'exemple vu plus haut:

llo n

L'opérateur IN est une façon raccourcie d'utiliser l'opérateur OR ou même la fonction CONTAINSROW( ).

É

Ajoutez comme critère que nous voulons uniquement les ordinateurs de type AST Intel 150 ou AST Intel 200.

Microsoft Power BI / Microsoft Excel BI

423/950

Vincent ISOZ

Solution

ch

an

ti

llo n

Voyons d'abord la solution avec un simple OR:

É

Ou écrit autrement:

Microsoft Power BI / Microsoft Excel BI

424/950

an

ti

llo n

Vincent ISOZ

É

ch

Ou avec l'opérateur IN:

Microsoft Power BI / Microsoft Excel BI

425/950

an

ti

llo n

Vincent ISOZ

É

ch

Ou encore avec CONTAINSROW( ):

Microsoft Power BI / Microsoft Excel BI

426/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

427/950

Vincent ISOZ

Exercice 12.: Fonction FILTER avec FOUND ou LEFT Excel BI 2010 à 2019

É

ch

an

ti

llo n

Considérons que nous voulons la somme des quantités par secteurs d'activités. Mais qu'une colonne sous forme de mesure DAX doit contenir les quantités vendues seulement pour les articles de type IBM.

Microsoft Power BI / Microsoft Excel BI

428/950

Vincent ISOZ

Solutions

llo n

La première solution possible est:

ch

an

ti

La deuxième solution possible est:

É

Il va sans dire que la même philosophie s'applique avec la fonction RIGHT( ).

Microsoft Power BI / Microsoft Excel BI

429/950

Vincent ISOZ

Exercice 13.: Fonction FILTER avec EVALUATE et ORDER BY Excel BI 2010 à 2019

É

ch

an

ti

llo n

Comme nous l'avons déjà précisé à la page 284, la requête EVALUATE n'existe à ce jour et à notre connaissance dans Power BI. Voyons toutefois un cas d'application avec Microsoft

Microsoft Power BI / Microsoft Excel BI

430/950

Vincent ISOZ

Exercice 14.: Fonction ALL Power BI 2018-03

É

ch

an

ti

llo n

Cette fonction renvoie toutes les lignes de la table en ignorant tous les filtres existants. Elle est utilisée en conjonction avec d'autres fonctions notamment CALCULATE( ), car même si cela ne se remarque pas, CALCULATE( ) crée un filtre contextuel lorsqu'elle est invoquée et parfois, il peut s'avérer utile de lever ce filtre.

Microsoft Power BI / Microsoft Excel BI

431/950

Vincent ISOZ

Solution

llo n

Le code ci-dessous calcule le rapport entre le volume des ventes brutes par le volume global des ventes. (On utilise ici DIVIDE( ) à la place de / pour éviter les erreurs de division par 0 notamment) :

an

ti

On peut ensuite afficher le résultat en conjonction avec les différents secteurs d'activité pour voir la part de chacun :

ch

Pour mieux comprendre tentons de présenter le numérateur et le dénominateur séparément : Le numérateur utilise tout simplement la mesure de calcul dans l'exercice de SUMX( ) :

É

Le dénominateur également :

Sauf, qu'avec ALL( ), on étend la somme à l'ensemble de la table ce qui permet à ce dernier de rester constant comme on peut le voir à l'affichage :

Microsoft Power BI / Microsoft Excel BI

432/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

433/950

Vincent ISOZ

Exercice 15.: Fonction ALL et REMOVEFILTER avec slicer (segment) ignoré et filtre statique supplémentaire Power BI 2018-03

llo n

Voici une requête typique très demandée. Comment créer une mesure qui soit indépendante de l'un des slicers (segments) et dépendante ou pas en même temps d'un autre filtre non dynamique?

É

ch

an

ti

Considérons que nous voulons afficher la somme des quantités par Article qui soit filtrable par année, mais aussi avec une colonne qui soit indépendante du filtre choisi pour une année?

Microsoft Power BI / Microsoft Excel BI

434/950

Vincent ISOZ

Solution

llo n

La solution est donnée par:

an

ti

Et considérons le cas avec un filtre statique supplémentaire comme quoi nous voulons que l'état des factures qui sont payées:

É

ch

Pour des raisons historiques et pédagogiques, Microsoft a créé la fonction REMOVEFILTER( ) qui dans cette situation se subtitue parfaitement à ALL( ) et serait plus performant!

Microsoft Power BI / Microsoft Excel BI

435/950

Vincent ISOZ

Exercice 16.: Fonction ALLSELECTED Power BI 2018-03

llo n

Cette fonction a la particularité de ne supprimer que les filtres contextuels alors que ALL( ) supprime tous les filtres même hiérarchiques!!!

Nous allons ici juste faire un exemple et plus tard, nous ferons des exercices sur cette fonction!

ch

Avec:

an

ti

Pour comprendre ce que fait cette fonction, nous conseillons de préparer le scénario suivant:

Et:

É

Et si on coche quelques articles au hasard, on obtient:

Microsoft Power BI / Microsoft Excel BI

436/950

llo n

Vincent ISOZ

On peut faire aussi une fonction DAX sur la même idée qui ne prend que ce qui n'est pas sélectionné. Prenons comme exemple la colonne Quantité avec:

É

ch

an

ti

Remarque: ALLSELECTED( ) et VALUES( ) ont le même effet tant qu'on travaille avec un seul filtre (ie une seule colonne!)

Microsoft Power BI / Microsoft Excel BI

437/950

Vincent ISOZ

Exercice 17.: Fonction ALLSELECTED avec calcul Power BI 2018-03

É

ch

an

ti

llo n

Considérons que nous souhaitons calculer le pourcentage des ventes mais relativement au grand total des ventes (quel que soit le filtre!).

Microsoft Power BI / Microsoft Excel BI

438/950

Vincent ISOZ

É

ch

an

ti

llo n

Solution

Microsoft Power BI / Microsoft Excel BI

439/950

Vincent ISOZ

Exercice 18.: Fonction ALLSELECTED avec colonnes multiples Power BI 2020-02

ch

an

ti

llo n

Considérons la matrice suivante:

É

Nous souhaitons ajouter une colonne qui pour chaque Secteur d'Activité compare en % par rapport au grand total, qui pour chaque état de Facture payée compare en % par rapport au grand total, mais qui pour les livreurs compare par rapport au grand total de chaque livreur! Bien évidemment tout en faisant en sorte que cela s'adapte à l'article sélectionné dans le segment contenant les Articles.

Microsoft Power BI / Microsoft Excel BI

440/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Pour cela nous créons le mesure suivante dans un premier temps (cela aide à comprendre):

Ensuite, pour mettre le tout en pourcentage, on modifie juste un petit peu comme ci-dessous:

Microsoft Power BI / Microsoft Excel BI

441/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

442/950

Vincent ISOZ

Exercice 19.: Fonction ALLSELECTED et EXCEPT( ) Power BI 2020-02

llo n

Créez une matrice avec un slicer sur les noms des Article qui montre: 1. La quantité vendue par Secteur d'Activité

2. Affiche pour chaque secteur d'activité une colonne avec le grand total

3. Affiche pour chaque secteur le grand total par Article sélectionné dans la slicer

4. Affiche pour chaque secteur la différence par rapport au grand-total par Article sélectionné dans la slicer

É

ch

an

ti

5. Affiche pour chaque secteur le total par Article sélectionné dans la slicer

Microsoft Power BI / Microsoft Excel BI

443/950

Vincent ISOZ

Solution

llo n

On va créer la matrice et le slicer suivant:

ti

Avec d'abord pour rappel:

ch

Et:

an

Et enfin:

Et:

É

Et:

Ce qui donne si on sélectionne par exemple deux articles:

Microsoft Power BI / Microsoft Excel BI

444/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

445/950

Vincent ISOZ

Exercice 20.: Fonction ALLSELECTED et ISONORAFTER pour les Running Total Power BI 2017-04

llo n

Ceci ne sera pas un exercice mais un exemple puisque depuis Avril 2004, nous n'avons plus besoin d'écrire de pattern DAX pour un tel calcul. De plus, depuis la création de IsOnOrAfter( ) le calcul de running Total c'est considérablement simplifié.

ti

Considérons que nous avons créé la matrice suivante:

É

ch

an

On va dans les options de champs de Power BI et on prend l'option new quick measure (sur lequel on reviendra en détails plus tard dans ce chapitre):

Microsoft Power BI / Microsoft Excel BI

446/950

an

ti

llo n

Vincent ISOZ

É

ch

Une fois ceci fait, on prend:

Microsoft Power BI / Microsoft Excel BI

447/950

ch

an

ti

llo n

Vincent ISOZ

É

Et on paramètre comme suit:

Microsoft Power BI / Microsoft Excel BI

448/950

ch

an

ti

llo n

Vincent ISOZ

É

On valide pour obtenir:

Microsoft Power BI / Microsoft Excel BI

449/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

450/950

Vincent ISOZ

Exercice 21.: Fonction DISTINCT Power BI 2018-03

É

ch

an

ti

llo n

Selon que l'argument soit une table ou une colonne cette fonction retourne une table ou une colonne avec suppression de doublons dans les lignes de la table ou cellules de la colonne.

Microsoft Power BI / Microsoft Excel BI

451/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Compter le nombre de pays concernés par notre base de données clients :

Microsoft Power BI / Microsoft Excel BI

452/950

Vincent ISOZ

Exercice 22.: Fonction ALLEXCEPT Power BI 2018-03

llo n

ALLEXCEPT( ) supprime tous les filtres de contexte de la table, à l'exception de ceux qui ont été appliqués aux colonnes spécifiées. Évidemment, dis comme cela c'est peu clair.

É

ch

an

ti

Comment alors obtenir la somme des quantités d'un article dans sa totalité mais pour chacun des secteurs d'activité?

Microsoft Power BI / Microsoft Excel BI

453/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution consiste "simplement" à créer une mesure du type suivant:

Microsoft Power BI / Microsoft Excel BI

454/950

Vincent ISOZ

Exercice 23.: Fonction HASONEVALUE avec application pour formatage Power BI 2018-03

llo n

La fonction HASONEVALUE( ) renvoie TRUE si sont paramètre est une table qui est faite d'une seule et unique ligne.

Pour illustrer cette fonction nous allons faire plusieurs exemples mais nous allons commencer par le cas bateau que tout le monde fait et qui n'est pas nécessairement le plus technique et le plus pertinent… L'idée est de créer un slicer qui permet à l'utilisateur de choisir s'il veut les données de ventes (somme nette avec escompte) de ses tableaux visibles sur les pages en: 1. Données réelles

3. Millions

an

4. ou autre…

ti

2. Milliers

É

ch

Essayez de deviner comment faire cela…

Microsoft Power BI / Microsoft Excel BI

455/950

Vincent ISOZ

Solution

an

ti

llo n

D'abord on crée une table en mode manuel soit en utilisant DATATABLE( ) (voir page 374) soit en utilisant UNION( ) et ROW( ) (voir page 370):

É

ch

Ensuite, on prépare sur une feuille la structure suivante (voir page 415):

Microsoft Power BI / Microsoft Excel BI

456/950

an

ti

llo n

Vincent ISOZ

É

ch

Ensuite, nous créons la mesure suivante:

Microsoft Power BI / Microsoft Excel BI

457/950

ti

llo n

Vincent ISOZ

É

ch

an

Il ne reste plus qu'ensuite à faire de nombreux IF avec des FORMAT pour formater le résultat plus esthétiquement….

Microsoft Power BI / Microsoft Excel BI

458/950

Vincent ISOZ

Exercice 24.: Fonction HASONEVALUE vs ISFILTERED vs HASONEFILTER Power BI 2019-10

É

ch

an

ti

llo n

Considérons la matrice suivante:

Microsoft Power BI / Microsoft Excel BI

459/950

É

ch

an

ti

llo n

Vincent ISOZ

Changer la source maintenant pour faire en sorte que une unique commande appartienne à un secteur que nous appelerons Agriculture (s'il n'existe pas déjà!) et rafraîchissez la matrice! Vous devriez alors avoir:

Microsoft Power BI / Microsoft Excel BI

460/950

É

ch

an

ti

llo n

Vincent ISOZ

Maintenant ajoutons la mesure DAX suivante:

Microsoft Power BI / Microsoft Excel BI

461/950

ch

an

ti

llo n

Vincent ISOZ

É

Et ici, nous pouvons avoir une surprise (du moins lorsqu'on y est confronté pour la première fois…). Pourquoi est-ce que Agriculture n'est vide? Eh bien cela provient de HASONEVALUE( ) lui-même! Comme l'indique son nom, chaque ligne de la matrice aura le calcul, si et seulement si l'élément ne correspond qu'à un seul élément, ou à un seul élément enfant! Comment y remédier?

Microsoft Power BI / Microsoft Excel BI

462/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution consiste à utiliser HASONEFILTER( ). Effectivement, crééz la mesure suivante:

Microsoft Power BI / Microsoft Excel BI

463/950

Vincent ISOZ

É

ch

an

ti

llo n

Mais alors qu'en est-il de ISFILTERED( )? Pour le comprender, rajoutez-le dans un premier temps:

On pourrait donc penser qu'il fait la même chose que HASONEFILTER( ). Eh bien non! Effectivement pour le voir, ajouter le segement des Articles sur la page du rapport:

Microsoft Power BI / Microsoft Excel BI

464/950

É

ch

an

Et cochez quelques articles… :

ti

llo n

Vincent ISOZ

Tout est dit…! Microsoft Power BI / Microsoft Excel BI

465/950

Vincent ISOZ

Exemple 25.: Requête ALLNOBLANKROW Power BI 2018-03

llo n

Le nom de cette requête est un piège. On pourrait penser qu'elle supprime les lignes vierges, mais il n'est est rien comme nous allons le voir sous la forme d'un exemple plutôt que d'un exercice!

PS: Pour supprimer les lignes vierges il n'y a pas à ce jour de solution élégante à notre connaissance. Il faut faire un bête filtre de type < > BLANK( ) sur de multiples colonnes…

ch

an

ti

Considérons notre schéma habituel suivant:

É

En se rappelant que dans la table SourceRemiseClients, nous avons des clients qui n'ont aucune vente dans la TablePrincipale. Donc si nous faisons:

Microsoft Power BI / Microsoft Excel BI

466/950

ch

an

ti

llo n

Vincent ISOZ

Une ligne vierge apparaît! C'est assez contre-intuitif comme résultat mais cela peut se comprendre comme un choix de Microsoft lorsqu'il existe une relation entre deux tables ou certaines données n'ont pas de correspondances!

É

Comment éliminer la ligne vierge à la fin?

Microsoft Power BI / Microsoft Excel BI

467/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Pour éviter l'apparition de cette ligne vide (qui pourrait être éliminée en une deuxième étape avec un filtre classique), on pourra utiliser donc la fonction ALLNOBLANKROW( ) comme illustré ci-dessous:

Ce qui facilite donc la tâche par rapport à l'époque où cette fonction n'existait pas et qu'il fallait utiliser un filtre avec un critère de type < > ISBLANK( ).

Microsoft Power BI / Microsoft Excel BI

468/950

Vincent ISOZ

Exercice 26.: Fonction USERELATIONSHIP Power BI 2018-03

an

ti

llo n

Considérons le schéma suivant:

É

ch

Et créons la matrice (tableau croisé dynamique) suivant sur un Dashboard:

Microsoft Power BI / Microsoft Excel BI

469/950

É

ch

an

ti

llo n

Vincent ISOZ

Nous voyons ici que par défaut, la Quantité Clean se base sur la relation active:

Microsoft Power BI / Microsoft Excel BI

470/950

É

ch

an

ti

llo n

Vincent ISOZ

Mais qu'en est-il si nous voulons les quantités sur les Date de paiement?

Microsoft Power BI / Microsoft Excel BI

471/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution est très importante et consiste à écrire:

Microsoft Power BI / Microsoft Excel BI

472/950

Vincent ISOZ

Exercice 27.: Fonction SELECTEDVALUE Power BI 2018-03

É

ch

an

ti

llo n

Nous souhaiterions créer un slicer qui permet de choisir le type de statistique que nous voulons pour une table qui regroupe les quantités par article.

Microsoft Power BI / Microsoft Excel BI

473/950

Vincent ISOZ

Solution

an

ti

llo n

D'abord nous créons une table manuelle:

É

ch

Et nous préparons ensuite un Dashboard comme ci-dessous:

L'idée étant de pouvoir maintenant créer une mesure qui dépend de la valeur sélectionnée dans le slicer:

Microsoft Power BI / Microsoft Excel BI

474/950

llo n

Vincent ISOZ

an

Et respectivement:

ti

Ce qui donnera bien:

É

ch

Notez que lorsque la fonction SELECTEDVALUE( ) n'existait pas cela, s'écrivait:

Microsoft Power BI / Microsoft Excel BI

475/950

Vincent ISOZ

Exercice 28.: Fonction INTERSECT avec SUM et CALCULATE Power BI 2019-10

llo n

Nous allons vouloir ici faire un calcul d'un type très rare. Nous souhaiterions calculer la somme des ventes par année entre des archives de ventes importées et la table de calendrier mais cependant sans créer de relation entre les deux tables.

an

ti

Pour cela, nous supposerons que nous avons importé les archives de ventes suivantes:

É

ch

Avec la table de calendrier suivante:

Microsoft Power BI / Microsoft Excel BI

476/950

an

ti

llo n

Vincent ISOZ

É

ch

Et nous ferons attention à ne pas lier les deux tables entre elles:

Microsoft Power BI / Microsoft Excel BI

477/950

an

ti

llo n

Vincent ISOZ

É

ch

Nou souhaiterions faire un analyse temporelle de ventes entre les deux tables sans les lier. Comment procéder?

Microsoft Power BI / Microsoft Excel BI

478/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

On crée la mesure suivante et la matrice suivante:

Microsoft Power BI / Microsoft Excel BI

479/950

Vincent ISOZ

Exercice 29.: Fonction TREATAS avec SUM et CALCULATE Power BI 2018-02

llo n

A priori la fonction TREATAS permet seulement de propager un filtre via deux tables qui ne sont pas liées physiquement (dans la pratique il faut à ce jour cependant toujours utiliser les relations physiques!).

Notez que TREATAS( ) fait la même chose que INTERSECT( ) à la différene de l'ordre dans lesquels s'écrivent les arguments!

Remarque: En réalité on peut créer des relations virtuelles avec TREATAS( ), INTERSECT( ) et aussi la fonction FILTER( ). Comme l'a montré Marco Russo sur sa page détaillée ici: https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/ les différences de performances suivant les choix sont cependant gigantesques!

ti

On pourrait refaire comme exercice, le même que nous avons fait avec la fonction INTERSECT( ) mais cela n'apporterait aucune plus value! Faisons quelque chose de plus exotique!

É

ch

an

Considérons que nous avons créé le table suivante en DAX (que nous aurions pu tout aussi bien créer avec Power Query!!!):

Microsoft Power BI / Microsoft Excel BI

480/950

an

ti

llo n

Vincent ISOZ

É

ch

Et le rapport suivant dans Power BI:

Microsoft Power BI / Microsoft Excel BI

481/950

ti

llo n

Vincent ISOZ

É

ch

an

Nous souhaiterions avoir une seule matrice et basculer de l'une à l'autre seulement via le slicer!

Microsoft Power BI / Microsoft Excel BI

482/950

Vincent ISOZ

Solution

llo n

On crée la mesure suivante:

É

ch

an

ti

Et ensuite on change les champs de la matrice comme visible ci-dessous:

Et on voit effectivement ci-dessus que lorsque les années sont sélectionnées, nous avons bien les années en colonnes. Et respectivement, si nous sélectionnons les articles, nous avons:

Microsoft Power BI / Microsoft Excel BI

483/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

484/950

Vincent ISOZ

Exercice 30.: Fonction ISINSCOPE Power BI 2018-02

an

ti

llo n

Je ne connais pas d'application à cette fonction en-dehors des visuels. Pour comprendre de quoi il s'agit, construisons le graphique suivant:

É

ch

Maintenant nous souhaitons mettre différentes lignes d'objectifs suivant le niveau de profondeur (drill down), que nous faisons dans le graphique.

Microsoft Power BI / Microsoft Excel BI

485/950

Vincent ISOZ

Solution

llo n

D'abord nous créons la mesure suivante (notez que ISINSCOPE( ) fonctionne avec n'importe quel type de champ et pas seulement avec une hiérarchie de champs de dates…!):

ch

an

ti

Ensuite, on change de graphique:

É

Et on ajoute notre mesure dans le champ Line Values. Ce qui donne:

Microsoft Power BI / Microsoft Excel BI

486/950

ti

llo n

Vincent ISOZ

Notez ici les problèmes avec les dates à cause du fait que cette mesure utilisant ISINSCOPE( ) n'est pas liée à la table calendrier. Le mieux alors de décomposer dans la table Table_Principales directement les dates concernées et d'utiliser ces dernières!

É

ch

an

Et si on fait un drill-down:

Microsoft Power BI / Microsoft Excel BI

487/950

Vincent ISOZ

Etc.

an

ti

llo n

Et encore und drill-down:

É

ch

On peut imaginer plein d'autres applications comme par exemple affichier des phrases particulières dans les info-bulles par défaut des graphiques en fonction du niveau de drill-down!

Microsoft Power BI / Microsoft Excel BI

488/950

Vincent ISOZ

DAX Catégorie Fonctions Statistiques Exercice 31.: Fonction SUM

llo n

Power BI 2018-03

La fonction SUM(), comme avec Microsoft Excel permet de calculer le total d'une colonne par exemple.

É

ch

an

ti

Nous allons déterminer la quantité totale de pièces commandées puis la somme totale par pays.

Microsoft Power BI / Microsoft Excel BI

489/950

Vincent ISOZ

Solution

llo n

Pour commencer, nous allons créer la variable Quantite_clean qui va contenir les quantités nettoyées en cliquant sur Modeling/New Column:

É

ch

an

ti

A ce niveau, on peut tout simplement glisser et déposer la nouvelle colonne pour voir la somme totale en faisant bien attention de sélectionner l'icône Table (encadré en rouge) :

Ou bien, on peut créer une mesure. Pour ce faire, on clique sur Modeling/New Mesure et on saisit la relation suivante :

Microsoft Power BI / Microsoft Excel BI

490/950

llo n

Vincent ISOZ

É

ch

an

ti

On peut ajouter aussi ce calcul en tant que colonne DAX:

Pour créer une table des quantités commandées par pays, il suffit simplement de glisser et déposer la colonne pays dans le tableau précédent. Ce qui donne le résultat suivant :

Microsoft Power BI / Microsoft Excel BI

491/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

492/950

Vincent ISOZ

Exercice 32.: Fonction SUMX (+RELATED) Power BI 2018-03

É

ch

an

Et donc la table suivante:

ti

llo n

Considérons le schéma suivant dans Power BI:

Microsoft Power BI / Microsoft Excel BI

493/950

ti

llo n

Vincent ISOZ

É

ch

an

On souhaite y rajouter une colonne qui affiche la somme des Quantités_Clean utilisant SUMX( ) et RELATED( ).

Microsoft Power BI / Microsoft Excel BI

494/950

Vincent ISOZ

Solution

ch

an

ti

llo n

La solution est donnée:

É

On peut complexifier l'exemple en faisant un calcul:

Microsoft Power BI / Microsoft Excel BI

495/950

an

ti

llo n

Vincent ISOZ

Fonctions AGGREGATEX SUMX AVERAGEX MINX MAXX COUNTX COUNTAX

É

ch

Notez que la même technique s'applique pour:

Microsoft Power BI / Microsoft Excel BI

496/950

Vincent ISOZ

Exercice 33.: Fonction SUMX (+FILTER +AND) Power BI 2018-03

É

ch

an

ti

llo n

La fonction SUMX( ) prend deux arguments, le premier doit être une table ou une fonction qui retourne une table (telles que VALUES( ) ou FILTER( )) puis le second argument doit contenir la colonne qui va être agrégé en somme. C'est une fonction itérative comme les autres fonctions en X (comme suffixe) …

Microsoft Power BI / Microsoft Excel BI

497/950

Vincent ISOZ

Solution

llo n

Exemple d'utilisation simple pour réaliser la somme totale des ventes Quantité * prix de toutes les commandes :

É

ch

an

ti

On a créé la mesure Somme_des_ventes_brutes qui vaut (glissez et déposez la mesure) :

Ou un autre cas très simple avec les quantités:

Microsoft Power BI / Microsoft Excel BI

498/950

an

ti

llo n

Vincent ISOZ

É

ch

Exemple d'utilisation combinée avec FILTER( ):

On peut ensuite glisser et déposer la mesure comme suit :

Microsoft Power BI / Microsoft Excel BI

499/950

an

ti

llo n

Vincent ISOZ

É

ch

En rajoutant à cette table la colonne pays, les ventes ou chiffre d'affaires de l'an 2000 par pays :

Microsoft Power BI / Microsoft Excel BI

500/950

ti

llo n

Vincent ISOZ

É

ch

an

Nous allons finalement déterminer la part du chiffre d'affaires net (en considérant cette fois les rabais) réellement encaissé en 2000, ce qui implique une double condition de filtrage (sur les dates de commandes 2000 et factures payées « OUI » en utilisant la fonction AND( ):

Microsoft Power BI / Microsoft Excel BI

501/950

Vincent ISOZ

Bien évidemment, nous avons aussi la fonction OR( )!

an

ti

llo n

On peut enfin mettre la mesure avec pays et surtout avec la colonne Facture payée pour constater qu'effectivement notre filtre à bien fonctionné :

Fonctions AGGREGATEX SUMX AVERAGEX MINX MAXX COUNTX COUNTAX

É

ch

Notez que la même technique s'applique aux autres fonctions d'agrégation:

Microsoft Power BI / Microsoft Excel BI

502/950

Vincent ISOZ

Exercice 34.: Interlude sur les SUM et SUMX Power BI 2018-03

llo n

Nous savons qu'il est facile en ajoutant un simple champ dans un dashboard Power BI d'obtenir:

ch

an

ti

Mais voyons qu'on peut obtenir la même chose autrement avec une mesure DAX:

É

Ou encore autrement avec une autre mesures DAX:

Microsoft Power BI / Microsoft Excel BI

503/950

Vincent ISOZ

an

ti

Ou encore autrement avec un colonne DAX:

llo n

Ou encore avec une autre mesure DAX gérant potentiellement les filtres:

É

ch

Remarquons que rien ne nous empêche de faire une colonne qui affiche toujours le grand total:

Et on peut faire un ratio:

Microsoft Power BI / Microsoft Excel BI

504/950

llo n

Vincent ISOZ

É

ch

an

ti

Ou faire un mesure DAX qui ne dépend pas d'un segement spécifique:

Microsoft Power BI / Microsoft Excel BI

505/950

Vincent ISOZ

Exercice 35.: Fonction AVERAGE Power BI 2018-03

É

ch

an

ti

llo n

Cette fonction tout simplement permet de réaliser la moyenne.

Microsoft Power BI / Microsoft Excel BI

506/950

Vincent ISOZ

Premier cas

É

ch

an

ti

llo n

On peut comme avec SUM( ) vu précédemment, créer une mesure pour la moyenne et la coupler avec une colonne de catégorielle par exemple le pays d'origine du client.

Microsoft Power BI / Microsoft Excel BI

507/950

Vincent ISOZ

Solution

llo n

Créer la mesure de la moyenne et la faire glisser dans un objet visuel Table :

É

ch

an

ti

On peut facilement après coup y ajouter la colonne des pays

Microsoft Power BI / Microsoft Excel BI

508/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

509/950

Vincent ISOZ

Deuxième cas Il s'agit d'un cas classique qui était souvent demandé dans les tableaux croisés dynamiques de Microsoft Excel (et non pas les Power Pivot) et à ma connaissance, impossible à faire sans de nombreux bricolages!

É

ch

an

ti

llo n

L'idée est comment avoir la moyenne par produit en excluant les quantités qui sont à "zéro".

Microsoft Power BI / Microsoft Excel BI

510/950

Vincent ISOZ

É

ch

an

ti

llo n

Solution

Microsoft Power BI / Microsoft Excel BI

511/950

Vincent ISOZ

Troisième cas

llo n

Considérons le scénario suivant:

On voit qu'il y au un problème. La moyenne n'est évidemment pas la moyenne des moyennes. Nous savons pourqouoi par ailleurs

É

ch

an

ti

Comme résoudre ceci?

Microsoft Power BI / Microsoft Excel BI

512/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution est traité un peu plus bas à la page 517.

Microsoft Power BI / Microsoft Excel BI

513/950

Vincent ISOZ

Exercice 36.: Fonction AVERAGEX Power BI 2018-03

llo n

Cette fonction est la version itérative de la fonction AVERAGE( ) et à l'instar de SUMX( ), et bien d'autres fonctions en « X » elle prend deux arguments : le premier un tableau et le second une expression renvoyant une colonne sur laquelle sera effectuée la moyenne.

É

ch

an

ti

Calculer la moyenne des ventes brutes par exemple est impossible à réaliser avec AVERAGE( ) donc il faut utiliser AVERAGEX( ) qui elle est itérative.

Microsoft Power BI / Microsoft Excel BI

514/950

Vincent ISOZ

llo n

Solution

É

ch

an

ti

Puis on glisse et on dépose la mesure dans une table comme ci-dessous :

Ensuite l'on peut comme avec la fonction SUMX( ), refaire la moyenne des ventes brutes en 2000 et la moyenne des ventes nettes (des réductions à l'occurrence le rabais) réellement encaissées en combinant AVERAGEX( ) avec FILTER( ). Notez que la même technique s'applique aux autres fonctions d'agrégation: Microsoft Power BI / Microsoft Excel BI

515/950

Vincent ISOZ

É

ch

an

ti

llo n

Fonctions AGGREGATEX SUMX AVERAGEX MINX MAXX COUNTX COUNTAX

Microsoft Power BI / Microsoft Excel BI

516/950

Vincent ISOZ

Exercice 37.: Moyenne Résumée, vs Moyenne Globale, vs Moyenne semi-locale Power BI 2018-03

É

ch

an

ti

llo n

Si nous considérons le tableau croisé dynamique suivant (un classique de Microsoft Excel aussi!):

Nous voyons bien qu'il y a un problème au niveau des sous-totaux qui n'est pas forcément souhaité. L'exercice consiste donc à corriger cela.

Microsoft Power BI / Microsoft Excel BI

517/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Si nous ajoutons la mesure suivante le résultat ne sera toujours pas celui attendu mais c'est une étape nécessaire (qui pourra être masquée par la suite!):

Une fois ceci fait, nous pouvons ajouter la vraie mesure qui nous intéresse et notez la présence de la fonction VALUES( ) qui est ici de première importance (!!!!) et de HASONVALUE( ) qui est aussi importante pour décider le niveau auquel nous faisons le calcul:

Microsoft Power BI / Microsoft Excel BI

518/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

519/950

Vincent ISOZ

Exercice 38.: Fonction MIN Power BI 2018-03

É

ch

an

ti

llo n

Comme son nom l'indique, elle permet de déterminer la valeur la plus petite d'une colonne fournie en argument. Elle peut prendre deux arguments, dans ce cas ces derniers sont des valeurs scalaires et non des colonnes.

Microsoft Power BI / Microsoft Excel BI

520/950

Vincent ISOZ

Solution

an

ti

llo n

Trouver la date de la première commande :

ch

Cas de deux arguments, par exemple déterminer la valeur minimale entre les ventes brutes moyennes de 2000 et 2001 :

É

On calcule d'abord la moyenne des ventes de 2000

Et la moyenne des ventes de 2001

Microsoft Power BI / Microsoft Excel BI

521/950

Vincent ISOZ

ch

an

ti

En affichant les résultats côte à côte on a ceci :

llo n

Le minimum :

É

En moyenne on a moins vendu en 2001 qu'en 2000.

Microsoft Power BI / Microsoft Excel BI

522/950

Vincent ISOZ

Exercice 39.: Fonction MINX Power BI 2018-03

É

ch

an

ti

llo n

La version MIN( ) des fonctions en « X », comme les autres, elle prend deux arguments : le premier un tableau et le second une expression renvoyant une colonne sur laquelle sera déterminée le minimum.

Microsoft Power BI / Microsoft Excel BI

523/950

Vincent ISOZ

Solution

llo n

La facture, la moins élevée :

ti

Affichage du résultat :

Notez que la même technique s'applique aux autres fonctions d'agrégation:

É

ch

an

Fonctions AGGREGATEX SUMX AVERAGEX MINX MAXX COUNTX COUNTAX

Microsoft Power BI / Microsoft Excel BI

524/950

Vincent ISOZ

Exercice 40.: Fonction MAX Power BI 2018-03

llo n

Comme MIN, cette fonction peut être utilisée sous deux formes. Soit elle détermine la valeur maximale dans une colonne ou soit elle détermine la valeur maximale entre deux scalaires.

É

ch

an

ti

Affichez dans une carte quelle est la date de la plus récente des commandes et ensuite par article la date de fin de mois de la dernière commande.

Microsoft Power BI / Microsoft Excel BI

525/950

Vincent ISOZ

Solutions Commençons par la date de la plus récente des commandes ?

an

ti

llo n

En affichage ça donne ceci :

É

ch

Et maintenant l'affichage par article de la date de fin de mois de la dernière commande:

Microsoft Power BI / Microsoft Excel BI

526/950

Vincent ISOZ

Exercice 41.: Fonction MAXX Power BI 2018-03

llo n

Fonction en « X » de MAX. voir plus haut la définition des fonctions en « X » avec SUMX, AVERAGEX, MINX …

É

ch

an

ti

Calculez le rabais le plus élévé dans une simple carte et les rabais les plus élévés par pays dans une table!

Microsoft Power BI / Microsoft Excel BI

527/950

Vincent ISOZ

Solutions

llo n

Calculer le rabais le plus élevé jamais accordé :

an

ti

Dont voici le résultat :

É

ch

On peut afficher le résultat par pays comme ceci :

Microsoft Power BI / Microsoft Excel BI

528/950

Vincent ISOZ

Exercice 42.: Calcul de l'étendue pour utilisation conjointe de MAX et MIN

ti

Considérons la simple matrice suivante:

llo n

Power BI 2018-03

É

ch

an

La question est comment calculer l'étendue à l'aide des fonctions MAX( ) et MIN( )?

Microsoft Power BI / Microsoft Excel BI

529/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La réponse est simplement:

Microsoft Power BI / Microsoft Excel BI

530/950

Vincent ISOZ

Exercice 43.: Utilisation d'un slicer d'intervalle de date avec MAX et MIN Power BI 2018-03

an

ti

llo n

Un autres cas très courant de l'utilisation de MAX( ) et MIN( ) est relative au slicer (segment suivant) de Power BI:

Souvent pour divers besoins plus ou moins tordus (mais justifié!). Les utilisateurs ont besoin d'extraire soit uniquement la valeur de début (à gauche) soit la valeur de fin (à droite), ou les deux en même temps pour faire de l'analyse par fenêtrage temporel.

É

ch

Comme exerice, écrivez deux mesures qui dans un visuel de type Card, affiche respectivement la date de début et de fin sélectionnée!

Microsoft Power BI / Microsoft Excel BI

531/950

Vincent ISOZ

Solution

an

ti

llo n

Voyon déjà pour la date de fin sélectionnée:

É

ch

Et c'est le même système pour la date de début:

Ensuite il y a plus qu'à mélanger cela avec les fonctions d'intelligence temporelles que nous verrons plus loin. Microsoft Power BI / Microsoft Excel BI

532/950

Vincent ISOZ

Exercice 44.: Fonction COUNT Power BI 2018-03

É

ch

an

ti

llo n

Cette fonction renvoie le nombre de lignes (non-vides/non-blank) contenant une valeur numérique ou chaîne de caractères ou même date. Elle prend donc en argument une colonne.

Microsoft Power BI / Microsoft Excel BI

533/950

Vincent ISOZ

Solution Le nombre de commandes réalisées :

É

ch

an

ti

llo n

On peut ensuite ajouter la colonne Pays comme ceci pour afficher le nombre de commandes par Pays :

Microsoft Power BI / Microsoft Excel BI

534/950

Vincent ISOZ

Exercice 45.: Fonction COUNT, CALCULATE et USERELATIONSHIP Power BI 2018-03

ch

an

ti

llo n

Considérons (cas hypeerrrrrrr important et très très demandé dans la pratique!!!!!):

Nous sommes là dans une situation typiquement problématique encore en 2019 avec Power BI (qui ne gère pas encore les relations multiples ou les tables fantômes comme Microsoft Access).

É

Et nous construisons:

Microsoft Power BI / Microsoft Excel BI

535/950

an

ti

llo n

Vincent ISOZ

É

ch

Nous notons ici un problème… Les deux colonnes donnent le même nombre de ventes alors que si on contrôle la source, c'est évident que cela ne devrait pas être le cas. L'erreur est pour la colonne Date de paiement parce que… c'est elle qui est concernée par la relation en traitillé:

Microsoft Power BI / Microsoft Excel BI

536/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

537/950

Vincent ISOZ

Solution La solution consiste à créer la mesure suivante:

É

ch

an

ti

llo n

Ce qui donne bien:

Microsoft Power BI / Microsoft Excel BI

538/950

Vincent ISOZ

Exercice 46.: Fonction COUNTX Power BI 2018-03

É

ch

an

ti

llo n

Elle prend en argument d'abord une table ou une expression dont le résultat est une table, puis ensuite une colonne ou expression sur laquelle, elle opère le comptage.

Microsoft Power BI / Microsoft Excel BI

539/950

Vincent ISOZ

Solution

llo n

Le nombre de commandes réalisées en 2000:

an

ti

On peut ensuite ajouter la colonne Pays comme ceci pour afficher le nombre de commandes par Pays mais cette fois en 2000 :

Fonctions AGGREGATEX SUMX AVERAGEX MINX MAXX COUNTX COUNTAX

É

ch

Notez que la même technique s'applique aux autres fonctions d'agrégation:

Microsoft Power BI / Microsoft Excel BI

540/950

Vincent ISOZ

Exercice 47.: Fonction COUNTA Power BI 2018-03

É

ch

an

ti

llo n

Celle-ci contrairement aux précédentes prend en compte les valeurs textuelles et même logiques… (et aussi seulement les lignes non-vides/non-blank).

Microsoft Power BI / Microsoft Excel BI

541/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Compter le nombre de lignes non vides de la colonne Quantité :

Microsoft Power BI / Microsoft Excel BI

542/950

Vincent ISOZ

Exercice 48.: Fonction COUNTAX Power BI 2018-03

É

ch

an

ti

llo n

Elle prend en argument d'abord une table ou une expression dont le résultat est une table, puis ensuite une colonne ou expression sur laquelle, elle opère le comptage avec COUNTA.

Microsoft Power BI / Microsoft Excel BI

543/950

Vincent ISOZ

Solution

llo n

Compter le nombre de commande provenant du secteur des Assurances :

an

ti

On peut ensuite présenter le résultat par date de commande en faisant bien attention de ne cocher que l'année comme le montre la figure ci-dessous :

É

ch

Notez que la même technique s'applique aux autres fonctions d'agrégation: Fonctions AGGREGATEX SUMX AVERAGEX MINX MAXX COUNTX COUNTAX

Microsoft Power BI / Microsoft Excel BI

544/950

Vincent ISOZ

Exercice 49.: Fonction COUNTROWS Power BI 2018-03

É

ch

an

ti

llo n

Cette fonction permet de compter de lignes contenues dans une table fournie en argument.

Microsoft Power BI / Microsoft Excel BI

545/950

Vincent ISOZ

Solution

llo n

Simple utilisation : compter le nombre de ligne ou de commande en 2001

ti

Utilisation complexe avec CALCULATE et FILTER : Compter le nombre de commandes ou de lignes pour l'année 2001 et provenant du secteur des Assurances

É

ch

an

Quand on affiche la mesure on obtient :

Microsoft Power BI / Microsoft Excel BI

546/950

Vincent ISOZ

Exercice 50.: Fonction COUNTBLANK Power BI 2018-12

llo n

Le seul but de l'existence de cette fonction est d'éviter aux utilisateurs de DAX de faire un COUNTROWS( ) avec un filtre sur les valeurs de type BLANK( ).

ch

an

ti

Comme cas pratique, rappelons que nous avons la table suivante:

É

Nous souhaiton compter pour combien de clients nous n'avons pas le numéro de téléphone!

Microsoft Power BI / Microsoft Excel BI

547/950

Vincent ISOZ

Solution

an

ti

llo n

La solution consiste simplement à créer la mesure suivante:

É

ch

On peut obtenir le même résultat avec:

Microsoft Power BI / Microsoft Excel BI

548/950

Vincent ISOZ

Exercice 51.: Fonction DISTINCTCOUNT Power BI 2018-03

É

ch

an

ti

llo n

Cette fonction permet de compter le nombre de valeurs distinctes contenues dans une colonne fournie en argument.

Microsoft Power BI / Microsoft Excel BI

549/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Déterminer le nombre de secteurs d'activité d'où proviennent les commandes :

Microsoft Power BI / Microsoft Excel BI

550/950

Vincent ISOZ

Exercice 52.: Fonction PERCENTILEX.INC Power BI 2018-03

É

ch

an

ti

llo n

Nous avons vu à la page 374 comment créer une table de centiles en DAX:

Ce que nous souhaitons ici c'est de construire les valeurs correspondantes à chaque centile dans une table DAX typiquement afin de construire les fameux Q-Q plot.

Microsoft Power BI / Microsoft Excel BI

551/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution est donnée par:

Évidemment l'approche est la même avec PERCENTILEX.EXC( ). Il va de soi qu'on peut ensuite ajouter cette table au tableau de bord.

Microsoft Power BI / Microsoft Excel BI

552/950

Vincent ISOZ

Cependant on peut se poser la question comment reperoduire cette table directement en DAX dans un rapport afin de pouvoir faire du filtrage avec des segments????

ch

an

ti

llo n

Pour cela on construit d'abord la table suivante:

É

Et enfin dans un rapport on ajouter cette colonne dans un visuel de type Table avec la fonction DAX suivante:

Microsoft Power BI / Microsoft Excel BI

553/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

554/950

Vincent ISOZ

Exercice 53.: Fonction PERCENTILE.INC Power BI 2018-03

É

ch

an

ti

llo n

Nous voulons dans un carte affichier le 10ème centile (inclus) des Quantités vendues!

Microsoft Power BI / Microsoft Excel BI

555/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution consiste à créer la mesure DAX suivante et la mettre dans une carte comme visible ci-dessous:

Microsoft Power BI / Microsoft Excel BI

556/950

Vincent ISOZ

Exercice 54.: Fonction TOPN simple Power BI 2018-03

llo n

Comme nous l'avons déjà mentionné, cette fonction renvoie une ligne contenant le nom des colonnes puis des expressions qui génère. Elle prend en argument : -

Un entier désignant les n premiers ou derniers éléments

-

Une table

-

Une colonne selon laquelle la table doit être ordonnée

-

Finalement le type d'ordre DESC ou ASC

É

ch

an

ti

Nous souhaiterions afficher dans un TCD la somme des trois plus grandes ventes par Quantité pour un Article donné.

Microsoft Power BI / Microsoft Excel BI

557/950

Vincent ISOZ

Solutions

ti

llo n

Les solutions sont données par (cas le plus courant car les gens copient/collent souvent d'Internet sans réfléchir et utilisent un mesure DAX intermédiaire inutile):

É

ch

an

Ou une manière équivalente ne nécessitant pas de mesure DAX:

Microsoft Power BI / Microsoft Excel BI

558/950

Vincent ISOZ

Exercice 55.: Fonction TOPN dans calcul Power BI 2018-03

llo n

Comme nous l'avons déjà mentionné, cette fonction renvoie une ligne contenant le nom des colonnes puis des expressions qui la génère. Elle prend en argument : -

Un entier désignant les n premiers ou derniers éléments

-

Une table

-

Une colonne selon laquelle la table doit être ordonnée

-

Finalement le type d'ordre DESC ou ASC

É

ch

an

ti

Nous souhaitons calculer la part des trois premières commandes sur le total des commandes en valeur (Quantité * Prix à l'unité).

Microsoft Power BI / Microsoft Excel BI

559/950

Vincent ISOZ

Solution

llo n

Pour répondre à l'exercice, nous écrirons:

É

ch

an

ti

L'affichage peut se faire par Pays dans ce cas, ce serait la part des 3 premiers par Pays :

Microsoft Power BI / Microsoft Excel BI

560/950

Vincent ISOZ

Exercice 56.: Fonction TOPN dans calcul avec paramètre dynamique Power BI 2018-03

É

ch

an

ti

llo n

Dans une table nous souhaiterions par article et dans une première colonne afficher la somme totale des quantités vendue et, dans une deuxième colonne, la part de la somme des n plus grandes quantités vendues via un paramètre.

Microsoft Power BI / Microsoft Excel BI

561/950

Vincent ISOZ

Solution

llo n

On crée d'abord un paramère:

ch

an

ti

Avec les paramètres:

É

On a alors automatiquement un segement qui apparaît à l'écran:

Microsoft Power BI / Microsoft Excel BI

562/950

llo n

Vincent ISOZ

É

ch

an

ti

Il ne reste plus qu'à écrire la fonction DAX correspondante:

Microsoft Power BI / Microsoft Excel BI

563/950

Vincent ISOZ

Exercice 57.: Fonction TOPN en tant que requête Power BI 2018-03

É

ch

an

ti

llo n

Comme l'illustre la figure suivante, nous pouvons aussi utiliser TOPN( ) en tant que filtre de requête d'une table simple ou d'une table groupée en DAX:

Microsoft Power BI / Microsoft Excel BI

564/950

Vincent ISOZ

Exercice 58.: Fonction RANKX simple avec ALL( ) Power BI 2018-03

É

ch

an

ti

llo n

Pour découvrir RANKX( ) considérons la table suivante:

Comment y rajouter une colonne des rangs des clients ?

Microsoft Power BI / Microsoft Excel BI

565/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution consiste à créer la mesure suivante (après avoir trié la colonne quantité Somme Quantité DAX dans l'ordre décroissant!):

Notez la différence entre le paramètre dense et skip:

Microsoft Power BI / Microsoft Excel BI

566/950

llo n

Vincent ISOZ

ti

On peut ensuite naturellement se demander comme faire un filtre DAX en tant que mesure qui affichera que les 5 premiers? Eh bien à ma connaissance il n'existe pas de solution si on ne désire pas passer par une requête DAX basée sur TOPN( ) comme l'illustre la requête suivante, à la différence qu'il faudrait d'abord faire un groupement:

É

ch

an

Mais en tant que mesure cela ne sera pas possible car RANKX( ) n'est pas une mesure d'agrégation! Il faudra donc dans un premier temps créer la mesure suivante:

Microsoft Power BI / Microsoft Excel BI

567/950

ch

an

ti

llo n

Vincent ISOZ

É

Et ensuite retirer les 2 premières mesures DAX du tableau pour avoir finalement:

Microsoft Power BI / Microsoft Excel BI

568/950

llo n

Vincent ISOZ

É

ch

an

ti

Remarque: Souvent (par faut d'avoir une solution simple et élégante même en 2020…), RANKX( ) est utilisé pour créer un colonne d'Index en DAX (dans l'idée de vouloir éviter Power Query).

Microsoft Power BI / Microsoft Excel BI

569/950

Vincent ISOZ

Exercice 59.: Fonction RANK.EQ Power BI 2018-03

ch

an

ti

llo n

Pour découvrir RANK.EQ( ) qui est une fonction utilisée (de mon expérience) uniquement dans les requêtes DAX, considérons la table suivante:

É

Nous souhaiterions crée une nouvelle affichant que les numéros de clients groupés avec la somme des Quantité et leur range dans une table DAX!

Microsoft Power BI / Microsoft Excel BI

570/950

Vincent ISOZ

Solution

ch

an

ti

llo n

D'abord nous créons la table suivante:

É

Et enfin nous créons la colonne DAX suivante pour arriver à notre résultat:

Microsoft Power BI / Microsoft Excel BI

571/950

ch

an

ti

llo n

Vincent ISOZ

É

Ce type de colonnes peut typiquement être utilisé comme colonne de tri dans les segments (slicers!).

Microsoft Power BI / Microsoft Excel BI

572/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

573/950

Vincent ISOZ

DAX Catégorie Fonctions de Temps Exercice 60.: Fonctions TODAY et NOW

llo n

Power BI 2018-03

Les fonctions TODAY( ) et NOW( ) sont particulièrement utilisées en DAX pour: •

Les filtres que nous verrons plus loin

•

La génération de calendriers

•

L'affichage d'une date du jour sur un Dashboard (ou d'une date qui correspond à la dernière mise à jour)

ti

Nous allons nous concentrer ici sur ce dernier aspect. Essayez à partir de ce que vous avez vu à la page 374 sur DATATABLE( ) de créer une table contenant seulement la date du jour et faites de même mais avec ROW( ).

É

ch

an

PS: Notez que ces fonctions ne s'actualisent que si et seulement si on clique sur le bout Refresh de Power BI!

Microsoft Power BI / Microsoft Excel BI

574/950

Vincent ISOZ

Solutions

llo n

Voyons le premier cas:

an

Et le troisième:

ti

Et le deuxième:

É

ch

Ou encore avec UTCNOW( ):

Microsoft Power BI / Microsoft Excel BI

575/950

Vincent ISOZ

Exercice 61.: Fonction WEEKDAY Power BI 2018-03

É

ch

an

ti

llo n

Un exercice illustrant cette commande dans un cas fameux est disponible à la page 359 et aussi à la page 690.

Microsoft Power BI / Microsoft Excel BI

576/950

Vincent ISOZ

Exercice 62.: Fonction YEAR Power BI 2018-03

llo n

Un exercice illustrant cette commande dans un cas fameux est disponible à la page 359 et aussi à la page 690.

an

ti

Sinon voici un exemple combinant TODAY( ), YEAR( ), CALCULATE( ) et SUM( ):

É

ch

Ou de manière équivalente avec FILTER( ) dont nous détaillerons plus loin à la page 421 les spécificités:

Microsoft Power BI / Microsoft Excel BI

577/950

Vincent ISOZ

Exercice 63.: Fonction MONTH Power BI 2019-10

É

ch

an

ti

llo n

Un exemple connu et fameux d'application est déjà donné à la page 359.

Microsoft Power BI / Microsoft Excel BI

578/950

Vincent ISOZ

Exercice 64.: Fonction EOMONTH Power BI 2019-09

É

ch

an

ti

llo n

Un exemple connu et fameux d'application est déjà donné à la page 359.

Microsoft Power BI / Microsoft Excel BI

579/950

Vincent ISOZ

Exercice 65.: Fonction DAY Power BI 2019-10

É

ch

an

ti

llo n

Un exemple connu et fameux d'application est déjà donné à la page 359.

Microsoft Power BI / Microsoft Excel BI

580/950

Vincent ISOZ

Exercice 66.: Fonction DATE et SUM et CALCULATE Power BI 2019-09

an

ti

llo n

Considérons les deux tables suivantes, d'abord la table tblXLSprincipale:

É

ch

Et la table calendrier tblCalendar suivante

Microsoft Power BI / Microsoft Excel BI

581/950

É

ch

an

Avec respectivement la relation:

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

582/950

an

ti

llo n

Vincent ISOZ

É

ch

Nous souhaiterions une mesure (dans une carte par exemple!) nommée Jour de référence, qui affiche la somme des quantités pour le jour du 2001-01-01!

Microsoft Power BI / Microsoft Excel BI

583/950

Vincent ISOZ

É

ch

an

ti

llo n

Solution

Microsoft Power BI / Microsoft Excel BI

584/950

Vincent ISOZ

Exercice 67.: Fonctions YEARFRAC et INT et TODAY Power BI 2019-09

an

ti

llo n

Considérons la table suivante des clients:

É

ch

Et le besoin suivant (très utile dans de nombreux domaines du marketing, de l'ingénierie, de la finance et autres…!): Quel est l'âge en années d'un élément par rapport à une date donnée et la date d'aujourd'hui.

Microsoft Power BI / Microsoft Excel BI

585/950

Vincent ISOZ

Solution La solution correcte consiste à écrire:

É

ch

an

ti

llo n

Ce qui donnera:

Microsoft Power BI / Microsoft Excel BI

586/950

Vincent ISOZ

Exercice 68.: Fonction DATEDIFF Power BI 2018-03

llo n

La fonction DATEDIFF( ) est plus souvent, selon mon expérience (!), utilisée dans les colonnes DAX et très rarement directement dans des mesures (mais si c'est le cas elle se retrouve dans une requête DAX interne à la mesure!). Elle est utile en gestion de projets (durée de tâches), finance (calculs d'intérêts entre deux dates), comptabilité (délais de paiement), logistique (temps de passage), etc.

É

ch

an

ti

Considérons que nous souhaitons calculer le nombre de jours entre les deux dates suivantes:

Microsoft Power BI / Microsoft Excel BI

587/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution est extrêmement simple:

Microsoft Power BI / Microsoft Excel BI

588/950

Vincent ISOZ

Exercice 69.: Fonctions PREVIOUSDAY et NEXTDAY Power BI 2018-03

llo n

Pour étudier la fonction PREVIOUSDAY( ) (et par analogie aussi avec NEXTDAY( ) ), on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

an

ti

Qui contient:

É

…

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)! Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous: Microsoft Power BI / Microsoft Excel BI

589/950

llo n

Vincent ISOZ

É

ch

an

ti

Nous souhaiterions dans un Dashboard afficher les lignes des dates avec le Prix total avec rabais mais aussi avec le Prix total avec rabais du jour d'avant mais aussi du jour d'après!

Microsoft Power BI / Microsoft Excel BI

590/950

Vincent ISOZ

Solution

ch

an

ti

llo n

Nous créons une mesure d'abord avec le jour d'avant utilisant la fonction PREVIOUSDAY( ):

É

Ajoutons-y maintenant avec le jour d'après avec NEXTDAY( ):

Microsoft Power BI / Microsoft Excel BI

591/950

ch

an

ti

llo n

Vincent ISOZ

É

Donc tout est conforme à la logique. Après y'a plus qu'à et y faut qu'on…

Microsoft Power BI / Microsoft Excel BI

592/950

Vincent ISOZ

Exercice 70.: Fonctions PREVIOUSMONTH et NEXTMONTH Power BI 2018-03

llo n

Pour étudier la fonction PREVIOUSMONTH ( ) (et par analogie aussi avec NEXTMONTH( ) ), on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

an

ti

Qui contient:

É

…

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)! Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous: Microsoft Power BI / Microsoft Excel BI

593/950

llo n

Vincent ISOZ

É

ch

an

ti

Nous souhaiterions dans un Dashboard afficher les lignes des dates avec le Prix total avec rabais mais aussi avec le Prix total avec rabais du mois d'avant mais aussi du mois d'après!

Microsoft Power BI / Microsoft Excel BI

594/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Nous créons une mesure d'abord avec le mois d'avant utilisant la fonction PREVIOUSMONTH( ):

Ajoutons-y maintenant avec le mois d'après avec NEXTMONTH( ):

Microsoft Power BI / Microsoft Excel BI

595/950

ch

an

ti

llo n

Vincent ISOZ

É

Donc tout est conforme à la logique. Après y'a plus qu'à et y faut qu'on… Attention!!! PREVIOUSMONT( ) et NEXTMONTH( ) renvoient TOUS les jours du mois précédent (respectivement suivant!)

Microsoft Power BI / Microsoft Excel BI

596/950

Vincent ISOZ

Exercice 71.: Fonctions PREVIOUSQUARTER et NEXTQUARTER Power BI 2018-03

llo n

Pour étudier la fonction PREVIOUSQUARTER( ) (et par analogie aussi avec NEXTQUARTER( ) ), on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

an

ti

Qui contient:

É

…

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)!

Microsoft Power BI / Microsoft Excel BI

597/950

Vincent ISOZ

ti

llo n

Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous:

É

ch

an

Nous souhaiterions dans un Dashboard afficher les lignes des dates avec le Prix total avec rabais mais aussi avec le Prix total avec rabais du trimestre d'avant mais aussi du trimestre d'après!

Microsoft Power BI / Microsoft Excel BI

598/950

Vincent ISOZ

Solution

ch

an

ti

llo n

Nous créons une mesure d'abord avec l'année d'avant utilisant la fonction PREVIOUSQUARTER( ):

É

Ajoutons-y maintenant le trimestre d'après avec NEXTQUARTER( ):

Microsoft Power BI / Microsoft Excel BI

599/950

an

ti

llo n

Vincent ISOZ

É

ch

Donc tout est conforme à la logique. Après y'a plus qu'à et y faut qu'on…

Microsoft Power BI / Microsoft Excel BI

600/950

Vincent ISOZ

Exercice 72.: Fonctions PREVIOUSYEAR et NEXTYEAR Power BI 2018-03

llo n

Pour étudier la fonction PREVIOUSYEAR( ) (et par analogie aussi avec NEXTYEAR( ) ), on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

an

ti

Qui contient:

É

…

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)! Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous: Microsoft Power BI / Microsoft Excel BI

601/950

llo n

Vincent ISOZ

É

ch

an

ti

Nous souhaiterions dans un Dashboard afficher les lignes des dates avec le Prix total avec rabais mais aussi avec le Prix total avec rabais de l'année d'avant mais aussi de l'année d'après!

Microsoft Power BI / Microsoft Excel BI

602/950

Vincent ISOZ

Solution

an

ti

llo n

Nous créons une mesure d'abord avec l'année d'avant utilisant la fonction PREVIOUSYEAR( ):

É

ch

Notez ce que nous avons si nous ajoutons le niveau des mois:

Microsoft Power BI / Microsoft Excel BI

603/950

É

ch

an

ti

llo n

Vincent ISOZ

Ajoutons-y maintenant l'année d'après avec NEXTYEAR( ):

Microsoft Power BI / Microsoft Excel BI

604/950

llo n

Vincent ISOZ

É

ch

an

ti

Si nous ouvrons le niveau hiérarchique inférieur, nous obtenons:

Microsoft Power BI / Microsoft Excel BI

605/950

ch

an

ti

llo n

Vincent ISOZ

É

Donc tout est conforme à la logique. Après y'a plus qu'à et y faut qu'on…

Microsoft Power BI / Microsoft Excel BI

606/950

Vincent ISOZ

Exercice 73.: Fonction SAMEPERIODLASTYEAR Power BI 2018-03

llo n

Pour étudier la fonction SAMPERIODLASTYEAR( ), on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

an

ti

Qui contient:

É

…

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)! Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous: Microsoft Power BI / Microsoft Excel BI

607/950

llo n

Vincent ISOZ

É

ch

an

ti

Nous souhaiterions dans un Dashboard afficher les lignes des dates (jusqu'au niveau Mois) avec le Prix total avec rabais mais aussi avec le Prix total avec rabais de la même période anuelle qu'avant!

Microsoft Power BI / Microsoft Excel BI

608/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Nous créons simplement:

Microsoft Power BI / Microsoft Excel BI

609/950

Vincent ISOZ

Exercice 74.: Fonction PARALLELPERIOD Power BI 2018-03

llo n

Pour étudier la fonction PARALLELPERIOD( ) on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

an

ti

Qui contient:

É

…

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)! Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous: Microsoft Power BI / Microsoft Excel BI

610/950

llo n

Vincent ISOZ

É

ch

an

ti

Nous souhaiterions dans un Dashboard afficher les lignes des dates (jusqu'au niveau Mois) avec le Prix total avec rabais mais aussi avec le Prix total avec rabais de la même période annuelle qu'avant et comparer ceci avec la fonction SAMPERIODLASTYEAR( )!

Microsoft Power BI / Microsoft Excel BI

611/950

Vincent ISOZ

Solution

llo n

D'abord nous recréons la fonction SAMEPERIODLASTYEAR( ) dans une matrice:

an

ti

Maintenant, nous ajoutons PARALLELPERIOD( ):

É

ch

Les chiffres semblent aberrants. Mais pour les comprendre, il faut augmenter la granularité de l'axe temporel. Augmenter cette dernière au niveau du mois ou trimestre ne suffira pas pour comprendre ce qu'il se passe comme l'illustre bien la capture d'écran ci-dessous:

Microsoft Power BI / Microsoft Excel BI

612/950

É

ch

an

ti

llo n

Vincent ISOZ

Non il va falloir mettre la granularité au niveau de la journée pour comprendre:

Microsoft Power BI / Microsoft Excel BI

613/950

É

ch

an

ti

llo n

Vincent ISOZ

Et donc là on comprend beaucoup mieux! On voit que la même chose se reproduit pour l'année 2002:

Microsoft Power BI / Microsoft Excel BI

614/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

615/950

Vincent ISOZ

Exercice 75.: Fonction DATEADD Power BI 2018-03

ch

an

ti

llo n

Faites-en sorte d'avoir la configuration suivante de votre modèle relationnel:

où la colonne Date de tbl_Calendar est liée à Date de commande.

É

Ensuite, créez le tableau suivant dans une des pages:

Microsoft Power BI / Microsoft Excel BI

616/950

llo n

Vincent ISOZ

É

ch

an

ti

Nous souhaiterions pouvoir avoir dans chaque ligne, la valeur de l'année précédente!

Microsoft Power BI / Microsoft Excel BI

617/950

Vincent ISOZ

Solution

an

ti

llo n

Pour ce faire il "suffit"… "simplement"… de créer la mesure suivante:

ch

où pour rappel, Quantite_clean est basée sur:

É

Ensuite, nous ajoutons la mesure qui nous intéresse:

Microsoft Power BI / Microsoft Excel BI

618/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

619/950

Vincent ISOZ

Exercice 76.: PARALLELPERIOD vs DATEADD vs SAMEPERIODLASTYEAR Power BI 2018-03

llo n

Pour comprendre la différence entre ces trois fonctions on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

an

ti

Qui contient:

É

…

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)! Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous: Microsoft Power BI / Microsoft Excel BI

620/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

621/950

Vincent ISOZ

Solution Bon évidemment la première différence qu'on notera tout de suite avant même de faire un exemple est que:

PARALLELPERIOD

Type Seulement l'année précédente Toute période partielle (jour, mois, trimestre, année) anté ou postérieure Seulement les périodes complètes (mois, trimestre, année) anté ou postérieure

llo n

Fonction SAMEPERIODLASTYEAR DATEADD

ti

Donc la fonction PARALLELPERIOD( ) est similaire à la fonction DATEADD( ), sauf que PARALLELPERIOD( ) renvoie toujours des périodes complètes au niveau de granularité donné au lieu des périodes partielles renvoyées par DATEADD( ).

an

Pour comparer les trois il suffira de créer respectivement les trois mesures suivantes:

É

ch

Ce qui donnera au niveau de l'année dans une matrice:

Au niveau deS trimestreS:

Microsoft Power BI / Microsoft Excel BI

622/950

an

ti

llo n

Vincent ISOZ

É

ch

Au niveau des mois:

Microsoft Power BI / Microsoft Excel BI

623/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

624/950

Vincent ISOZ

É

ch

an

ti

llo n

Et pour bien comprendre ce qu'il se passe, une analyse au niveau des jours pour 2001 et plus particulièrement en Février devrait suffire:

Microsoft Power BI / Microsoft Excel BI

625/950

Vincent ISOZ

Exercice 77.: Fonctions DATESMTD et DATESYTD Power BI 2018-03

llo n

Pour étudier la fonction DATESMTD( ) (et respectivement DATESYTD( )), on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

an

ti

Qui contient:

É

…

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)! Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous: Microsoft Power BI / Microsoft Excel BI

626/950

llo n

Vincent ISOZ

É

ch

an

ti

Nous souhaiterions dans un Dashboard afficher les lignes des dates avec le Prix total avec rabais mais aussi avec Prix total avec rabais en cumul du jour par mois (MTD) et après de jour par année (YTD)!

Microsoft Power BI / Microsoft Excel BI

627/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

Nous créons simplement pour le cumul de jour par mois avec DATESMTD( ):

Où la fin de l'année 2000 ressemble à:

Microsoft Power BI / Microsoft Excel BI

628/950

ch

an

ti

llo n

Vincent ISOZ

É

Nous créons simplement pour le cumul de jour par année avec DATESYTD( ):

Microsoft Power BI / Microsoft Excel BI

629/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

630/950

Vincent ISOZ

Exercice 78.: Fonctions FIRSTDATE et LASTDATE

É

ch

an

ti

llo n

Power BI 2018-03

Microsoft Power BI / Microsoft Excel BI

631/950

Vincent ISOZ

Premier cas

llo n

Pour étudier la fonction FIRSTDATE( ) (et respectivement LASTDATE( ), on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

…

an

ti

Qui contient:

É

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)! Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous:

Microsoft Power BI / Microsoft Excel BI

632/950

llo n

Vincent ISOZ

É

ch

an

ti

Nous souhaiterions dans un Dashboard afficher les lignes des numéros de clients, et pour chaque client afficher la date de première commande et dans une autre colonne la date de dernière commande.

Microsoft Power BI / Microsoft Excel BI

633/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution à la première question sera (pour information la fonction MIN( ) donne exactement le même résultat!!!!):

Et la réponse à la deuxième sera simplement (pour information la fonction MAX( ) donne exactement le même résultat!!!!):

Microsoft Power BI / Microsoft Excel BI

634/950

ch

an

ti

llo n

Vincent ISOZ

É

Si on veut éliminer la ligne sans clients (piège qui se trouve dans le fichier), on mettra:

Microsoft Power BI / Microsoft Excel BI

635/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

636/950

Vincent ISOZ

Deuxième cas

llo n

Considérons notre table calendrier simplifiée:

É

ch

an

ti

En utilisant les deux fonctions FIRSTDATE( ) ET LASTDATE( ) faites en sorte que la date de début et de fin correspondent respectivement à la plus ancienne et la plus récente date d'achat de la table:

Microsoft Power BI / Microsoft Excel BI

637/950

Vincent ISOZ

Solution

llo n

La solution consiste à écrire:

an

ti

On trouvera souvent dans la pratique la forme plus complète suivante:

É

ch

Nous verrons plus bas (voir page 813) également un application de LASTDATE( ) pour calculer le moyenne mobile!

Microsoft Power BI / Microsoft Excel BI

638/950

Vincent ISOZ

Exercice 79.: Fonction ENDOFMONTH et CLOSINGBALANCEMONTH Power BI 2018-03

llo n

Pour étudier la fonction ENDOFMONTH( ) (et respectivement CLOSINGBALANCEMONTH( )), on conseillera fortement au lecteur de travailler avec le fichier Microsoft Excel suivant:

ch

an

ti

Qui contient:

É

…

Le lecteur remarquera que les dates de Date de Commande y sont continues (pour simplifier l'exemple!)!

Microsoft Power BI / Microsoft Excel BI

639/950

Vincent ISOZ

ti

llo n

Ensuite avec la fonction CALENDARAUTO( ), on demandera au lecteur de créer un table Calendrier et de la lier comme ci-dessous:

É

ch

an

Nous souhaiterions dans un Dashboard afficher les lignes des dates par année, mois et jour, et reporter uniquement le montant du dernier jour des mois à chaque niveau!

Microsoft Power BI / Microsoft Excel BI

640/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution est simplement pour ENDOFMONTH( ) donnée par:

Et pour CLOSINGBALANCEMONTH( ) (qui est simplement une autre variante d'écriture de ENDOFMONTH( )!!!):

Microsoft Power BI / Microsoft Excel BI

641/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

642/950

Vincent ISOZ

Exercice 80.: Fonction TOTALX to Date sans date fiscale (TOTALMTD, TOTALQTD, TOTALYTD) Power BI 2018-03

llo n

Les fonctions TOTALXTD( ) où X désigne la périodicité (Mensualité, trimestrialité, annualité) permettent de réaliser des cumuls des valeurs dans une colonne par intervalle de X.

Elles prennent au minimum 2 arguments une opération (somme, moyenne …etc) et une colonne de date obligatoirement continue.

É

ch

an

ti

Par exemple, nous allons réaliser le cumul des quantités vendues en 2000 par mois et par trimestre en prenant pour base l'année civile comme années fiscale.

Microsoft Power BI / Microsoft Excel BI

643/950

Vincent ISOZ

Solution

llo n

Le plus souvent en pratique comme pour nos données, la colonne de Date Commande ne présente pas des dates continues, ce qu'on fait c'est de créer une table, ici DimDate à part qui elle contient une colonne de date continue :

ch

an

ti

Puis il faut ensuite lier cette colonne Date à celle de la Date Commande comme ceci :

É

Ensuite il faut tout simplement codifier les mesures suivantes

Ces deux mesures peuvent avec la colonne Quantite_Clean et Date être placée dans une matrice. Il faut ensuite s'assurer de ne filtrer que l'année 2000 (c'est plus sympa!) comme le montre l'image suivante : Microsoft Power BI / Microsoft Excel BI

644/950

an

ti

llo n

Vincent ISOZ

É

ch

Finalement on obtient le résultat suivant :

Microsoft Power BI / Microsoft Excel BI

645/950

Vincent ISOZ

É

ch

an

ti

llo n

Ou sous forme graphique (c'est plus sympa et parlant!):

Microsoft Power BI / Microsoft Excel BI

646/950

Vincent ISOZ

Exercice 81.: Fonction TOTALX to Date avec date fiscale (TOTALMTD, TOTALQTD, TOTALYTD) Power BI 2018-03

llo n

Les fonctions TOTALXTD( ) où X désigne la périodicité (mensualité, trimestrialité, annualité) permettent de réaliser des cumuls des valeurs dans une colonne par intervalle de X comme nous l'avons déjà.

Elles prennent au minimum 2 arguments une opération (somme, moyenne …etc) et une colonne de date obligatoirement continue. Mais elles ont aussi deux autres arguments: 1. Filtre 2. Fin années fiscale

É

ch

an

ti

Pour voir un exemple, rappelons ce que nous avons fait précédemment:

Ou vue en trimestres:

Microsoft Power BI / Microsoft Excel BI

647/950

llo n

Vincent ISOZ

É

ch

an

ti

Considérons maintenant que nous voulons que l'année fiscale termine le 30 Juin, comment changer la formule que nous avions fait?

Microsoft Power BI / Microsoft Excel BI

648/950

Vincent ISOZ

Solution

an

ti

llo n

La solution réside simplement à changer un petit peut la formule en écrivant:

Même si ce n'est pas forcément évident au premier coup d'œil, on observe bien que le total year to date recommence à chaque troisième trimestre!

É

ch

Ce se voit évidemment encore mieux si on zoom plus!

Microsoft Power BI / Microsoft Excel BI

649/950

Vincent ISOZ

Exercice 82.: Fonction DATESBETWEEN Power BI 2018-03

llo n

La fonction DATESBETWEEN( ), permet de sélectionner les lignes de table pour lesquelles les dates sont comprises entre une date de début et une date de fin. Donc elle prend trois arguments, une colonne de date, une date de début et une date de fin.

É

ch

an

ti

L'exemple suivant permet de calculer les ventes brutes s'écoulant sur le premier semestre 2003.

Microsoft Power BI / Microsoft Excel BI

650/950

Vincent ISOZ

Solution

llo n

Pour se faire on utilise la fonction SUMX( ) pour réaliser l'opération de produit entre les quantités et les prix. C'est CALCULATE( ) qui se charge d'appliquer le filtre que représente le résultat de DATESBETWEEN( ):

an

ti

On peut ensuite afficher le résultat comme ceci :

É

ch

Ceci ce n'est nécessairement l'exemple le plus parlant. Comme nous allons le voir un peu plus loin avec la fonction DATESINPERIOD( ) dont le concept est similaire (mais avec une philosophie un tant soit peu différente).

Microsoft Power BI / Microsoft Excel BI

651/950

Vincent ISOZ

Exercice 83.: STARTOFX et ENDOFX Power BI 2018-03

llo n

Ce sont des fonctions qui renvoient la date de début dans le cas de STARTOFX( ) et la date de fin dans le cas de ENDOFX( ) d'une période X donnée. Ainsi, X peut être MONTH, QUARTER et YEAR.

É

ch

an

ti

Elles sont particulièrement intéressantes combinées notamment avec DATESBETWEEN, comme le montre l'exemple ci-dessous.

Microsoft Power BI / Microsoft Excel BI

652/950

Vincent ISOZ

Solution On veut calculer la part des quantités commandées par mois dans le total de quantités commandées en 2000.

É

ch

an

ti

On s'assure que le filtrage suivant est respecté :

llo n

On entre la mesure suivante utilisant donc STARTOFYEAR( ) et ENDOFYEAR( ) :

Microsoft Power BI / Microsoft Excel BI

653/950

Vincent ISOZ

Exercice 84.: Fonction DATESINPERIOD et ENDOFMONTH Power BI 2018-03

ch

an

ti

llo n

Le concept de DATESINPERIOD( ) est similaire mais toutefois un peu différent dans sa philosophie. Pour l'introduire, considérons le schéma suivant:

É

Et la construction suivante d'une matrice (tableau croisé dynamique):

Microsoft Power BI / Microsoft Excel BI

654/950

É

ch

an

ti

llo n

Vincent ISOZ

Considérons que nous souhaitons afficher les sommes des Quantités mais cumulées sur une période de dates (pars mois en l'occurrence) mais avec un décalage de -3 mois. Microsoft Power BI / Microsoft Excel BI

655/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution consiste donc à créer la mesure DAX suivante:

Microsoft Power BI / Microsoft Excel BI

656/950

Vincent ISOZ

É

ch

an

ti

llo n

Notez les problèmes au niveau des sous-totaux… pour corriger cela il faudrait alors écrire en utilisant HASONEVALUE( ) et BLANK( ):

Évidemment, on peut procéder de même avec STARTOFMONTH( ).

Microsoft Power BI / Microsoft Excel BI

657/950

Vincent ISOZ

Exercice 85.: Fonctions DATESBETWEEN( ) vs DATESINPERIOD( ) Power BI 2019-12

llo n

Le but va être ici de déterminer le flou qui existe entre les deux fonctions nommées dans le titre car outre le fait que leur syntaxe soit différente, il y a une évidente possible confusion. D'abord rappelons la syntaxe des deux fonctions: •

DATESINPERIOD(,<start_date>,,)

•

DATESBETWEEN(,<start_date>,<end_date>)

On voit dans un premier temps que la deuxième est plus facile à utiliser que la deuxième, cependant la première à des paramètres intéressents qui font que l'on peut jouer avec via des slicers (segments).

É

ch

an

ti

Maintenant pour comparer la syntaxe des deux, créez la mesure suivante:

Créez la mesure équivalente avec DATESBETWEEN( )! Microsoft Power BI / Microsoft Excel BI

658/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution est:

On voit donc que la manière de compter les dates n'est pas similaire entre les deux fonctions. Il faut donc être très prudent et toujours faire une relecture des résultats pour ne pas se tromper!

Microsoft Power BI / Microsoft Excel BI

659/950

Vincent ISOZ

Exercice 86.: Fonction PREVIOUSX et NEXTX Power BI 2018-03

llo n

Ces fonctions renvoient la table contenant les lignes précédentes dans le cas de PREVIOUSX( ) et les lignes suivantes dans le cas NEXTX( ) par rapport à la période X. X ici peut être DAY, MONTH, QUATER et YEAR. Donc l'exemple suivant s'applique typiquement aux fonctions: NEXTDAY( )

•

NEXTMONTH( )

•

NEXTYEAR( )

•

PREVIOUSDAY( )

•

PREVIOUSMONTH( )

•

PREVIOUSNEXTYEAR ( )

an

ti

•

É

ch

L'exemple suivant permet de calculer la variation relative entre les ventes mensuelles au cours de l'an 2000.

Microsoft Power BI / Microsoft Excel BI

660/950

Vincent ISOZ

Solution

llo n

La part des ventes mensuelles dans les ventes totales réalisées au cours de l'année utilisant STARTOFYEAR( ) et ENDOFYEAR( ) :

ch

an

ti

La mesure de la variation relative entre les ventes mensuelles (notez l'utilisation des fonctions ISBLANK( ), PREVIOUSMONTH( )) :

É

Il faut ensuite s'assurer de réaliser le filtrage suivant pour seulement l'année 2000 :

Microsoft Power BI / Microsoft Excel BI

661/950

ti

llo n

Vincent ISOZ

É

ch

an

Finalement la visualisation tabulaire donne ceci :

Microsoft Power BI / Microsoft Excel BI

662/950

Vincent ISOZ

Exercice 87.: Fonction FIRSTNONBLANK Power BI 2018-03

ch

an

ti

llo n

Considérons que nous avons importé la table suivante:

É

Et que nous avons le schéma suivant:

Microsoft Power BI / Microsoft Excel BI

663/950

llo n

Vincent ISOZ

É

ch

an

ti

Nous souhaiterions dans la première table rapatrier la première vente de chaque vendeur. Comment faire?

Microsoft Power BI / Microsoft Excel BI

664/950

Vincent ISOZ

Solution

ch

an

ti

llo n

La solution est de créer une colonne DAX avec:

Évidemment le principe est exactement le même avec LASTNONBLANK( ).

É

On peut ensuite utiliser ce type de valeurs pour faire des filtrages dans CALCULATE( ):

Microsoft Power BI / Microsoft Excel BI

665/950

É

ch

an

ti

Ce qui correspond bien au contenu de la table:

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

666/950

Vincent ISOZ

Exercice 88.: Fonction EARLIER Power BI 2019-06

llo n

Nous souhaiterions en DAX: Créer une table (requête DAX) avec les colonnes Date de paiement et Quantité Clean

•

Créer une seconde table qui groupe la précédente par dates identiques, fait la somme des quantités et en même temps élimine les lignes vides

•

Et enfin… une colonne dans cette dernière table qui fait le cumul temporel

É

ch

an

ti

•

Microsoft Power BI / Microsoft Excel BI

667/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La première étape est simple, nous savons qu'il suffit d'utiliser la fonction SELECTCOLUMNS( ):

Maintenant en faisant le groupement, cela éliminera en même temps la ligne vide (notez que pour des raisons esthétiques, nous avons trié les dates dans l'ordre croissant):

Microsoft Power BI / Microsoft Excel BI

668/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

669/950

Vincent ISOZ

an

ti

llo n

Maintenant il nous reste le dernier point, qui est de faire la somme cumulée par date avec la fonction EARLIER( ):

ch

Attention, la fonction EARLIER( ) nécessite soit une colonne de date soit une colonne avec un Index. Remarques:

1. Il est possible aussi d'utiliser des variables en lieu et place et EARLIER( )

É

2. On peut faire un équivalent de EARLIER( ) avec Power Query en ajoutant un Index dans une table, en ajoutant une colonne avec INDEX-1 dans cette même table, ensuite en liant la table avec elle-même1 (et en rapatriant les données désirées).

1

Une relation circulaire en d'autres termes…

Microsoft Power BI / Microsoft Excel BI

670/950

Vincent ISOZ

Exercice 89.: Fonction EARLIER pour "SmartIndex" Power BI 2020-05

llo n

Nous souhaiterions en DAX à partir de notre table des ventes avec leur numéro de commande (assimilable à une clé d'index), calculer les différences lignes par ligne dans la table elle-même (et non dans un table d'un rapport!).

É

ch

an

ti

Pour cela nous partirons de notre table TablePrincipale dont nous aurons gardé que les colonne suivantes:

et que nous renommerons TablePrincipale avec SmartIndex. Microsoft Power BI / Microsoft Excel BI

671/950

Vincent ISOZ

Solution (méthode "ancienne" avec EARLIER( ))

É

ch

an

ti

llo n

La première solution considérée comme ancienne est donnée par:

Microsoft Power BI / Microsoft Excel BI

672/950

Vincent ISOZ

Solution (méthode "moderne" sans EARLIER( ))

É

ch

an

ti

llo n

La deuxième solution considérée comme moderne est donnée par:

Microsoft Power BI / Microsoft Excel BI

673/950

Vincent ISOZ

Exercice 90.: Mois précédent filtré Power BI 2019-06

llo n

Nous souhaiterions en DAX: Afficher une matrice qui par année et par mois afficher les quantités vendues

•

Ajouter un slicer avec la possibilité de choisir plusieurs combinaisons Mois-Année

•

Ajouter une colonne dans la matrice qui par rapport aux sélections faites dans le slicer, afficher la valeur du mois précédent visible dans la matrice

É

ch

an

ti

•

Microsoft Power BI / Microsoft Excel BI

674/950

Vincent ISOZ

Solution

ch

an

ti

llo n

D'abord on prépare le scénario de base qui ne devrait poser aucune difficulté:

É

Ce qu'on veut c'est le résultat suivant:

Microsoft Power BI / Microsoft Excel BI

675/950

ti

llo n

Vincent ISOZ

É

ch

an

Pour arriver à ce résultat, nous avons écrit la fonction non-triviale suivante:

Microsoft Power BI / Microsoft Excel BI

676/950

Vincent ISOZ

DAX Catégorie Fonctions d'Information et Parent/Enfant Exercice 91.: Fonction LOOKUPVALUE pour relation circulaire

llo n

Power BI 2018-03

Attention! Il y a des utilisateurs qui utilisent LOOKUPVALUE( ) en lieu et place de RELATED( ) alors que cette dernière est nettement plus rapide! Cependant il y a des situations comme le cas des relations circulaires ci-dessous, où l'usage de LOOKUPVALUE( ) reste à ce jour (et à notre connaissance) indispensable!

an

ti

Nous souhaiterons dans une page Power BI une colonne qui fait les différences ligne à ligne comme l'illustre par exemple la troisième colonne dans l'exemple ci-dessous (exemple différent de celui qui j'attends dans cet exercice!):

É

ch

C'est un calcule typique des lag financiers en trading pour calculer les log-return, mais ce n'est de loin pas la seule application!

Microsoft Power BI / Microsoft Excel BI

677/950

Vincent ISOZ

Solution en Power Query La solution n'est guère simple… Et de mon point de vue impossible à deviner… C'est beaucoup plus simple de la faire uniquement en pur Power Query…

an

ti

llo n

D'abord il va falloir dupliquer la requête dans Power Query et la synthétiser en ajoutant une colonne d'index comme visible ci-dessous (je n'ai pas encore trouvé à ce jour une manière élégante de créer cela en DA!!!):

Notez que les Secteurs d'Activité (Activity) sont triés dans l'ordre alphabétiques et donc que le numéro d'index est numéroté dans cet ordre!

É

ch

En DAX la même chose doit se faire en deux étapes à ma connaissance mais échoue s'il y a des égaltiés!. D'abord on crée une table résumée (rappelons qu'on ne peut trier à ce jour que manuellement en DAX Power BI!!!):

Microsoft Power BI / Microsoft Excel BI

678/950

Vincent ISOZ

an

ti

llo n

Et ensuite on ajoute une colonne DAX utilisant FILTER( ) et EARLIER( ), où là encore nous avons trié manuellement juste pour des raisons visuelles même si ce n'est pas nécessaire pour la suite!:

Remarquez que comme il y a des valeurs égales, on échouera ici à faire un lag correct!!!

É

ch

Ensuite, on crée une colonne (oui ici le nom choisi laisse à désirer…) avec la formule suivante:

Microsoft Power BI / Microsoft Excel BI

679/950

ti

llo n

Vincent ISOZ

É

ch

an

On devine ici qu'on est arrivé au résultat attendu. Il suffit alors d'ajouter cette "table-requête" dans le tableau de bord Power BI:

Microsoft Power BI / Microsoft Excel BI

680/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

681/950

Vincent ISOZ

Exercice 92.: Fonction LOOKUPVALUE pour sélection de scénarios Power BI 2018-03

an

ti

llo n

Considérons la table manuelle suivante:

É

ch

Et le slicer, carte et tableau correspondant:

Nous souhaiterions une colonne Prix total avec rabais avec taxe.

Microsoft Power BI / Microsoft Excel BI

682/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution utilise donc LOOKUPVALUE( ) mais aussi la non moins fameuse fonction SELECTEDVALUE( ):

Microsoft Power BI / Microsoft Excel BI

683/950

Vincent ISOZ

Exercice 93.: Fonction PATH Power BI 2018-03

É

ch

an

ti

llo n

Considérons la table importée et modifiée suivante qui nous est bien connue (nous avons enlevé la colonne avec les liens internet vers les images):

La question étant: comment pouvons-nous comme en SQL, afficher la hiérarchie complète? Microsoft Power BI / Microsoft Excel BI

684/950

Vincent ISOZ

É

ch

an

ti

llo n

Solution

Microsoft Power BI / Microsoft Excel BI

685/950

Vincent ISOZ

Exercice 94.: Fonction LOOKUPVALUE et PATHITEM Power BI 2018-03

É

ch

an

ti

llo n

Considérons la table DAX précédemment construite:

Pour les besoins de la construction des tableaux croisés dynamiques (matrices) sur les tableaux de bords, nous souhaitons décomposer la Hiérarchie dans des colonnes spécifiques.

Microsoft Power BI / Microsoft Excel BI

686/950

Vincent ISOZ

É

ch

an

ti

llo n

Faites-cela pour les 2 premiers niveaux mais en reprenant le nom des clients au lieu du numéro de hiérarchie!

Microsoft Power BI / Microsoft Excel BI

687/950

Vincent ISOZ

Solution

É

ch

an

ti

llo n

La solution pour le premier niveau sera:

Et pour le deuxième niveau:

Microsoft Power BI / Microsoft Excel BI

688/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

689/950

Vincent ISOZ

DAX Catégorie Fonctions de Texte Il faut savoir que la majorité des fonctions de textes sont utilisées soit lors de la création de tables, soit en tant que DAX de colonnes (contexte de lignes). Ne soyez pas surpris de ne le voir que très rarement dans en tant que mesure!

llo n

Exercice 95.: Fonction FORMAT (+WEEKDAY, ADDCOLUMNS et YEAR et DAY( ) ) Power BI 2018-03

Nous voulons rajouter dans la table calendrier, de nombreuses informations complémentaires comme le nom du mois, le nom du jour, le trimestre, etc.

É

ch

an

ti

Procédez comme demandé en utilisant les fonctions CALENDAR( ), FORMAT( ) et WEEKDAY( ) et la fonction de création de table ADDCOLUMNS( ).

Microsoft Power BI / Microsoft Excel BI

690/950

Vincent ISOZ

ti

llo n

Solution

an

Ou une version alternative que nous vous autorisons à copier/coller:

É

ch

tbl_Calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "Semester", "S" & IF ( MONTH ( [Date] ) <= 6, 1, 2 ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date], 2 ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "WeekNumber", "Week" & WEEKNUM ( [Date], 2 ), "Week", WEEKNUM ( [Date], 2 ), "ISO 8601 Week", WEEKNUM ( [Date], 21 ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )

Microsoft Power BI / Microsoft Excel BI

691/950

Vincent ISOZ

Tables des valeurs par défauts de la fonction FORMAT

Description

"General Number"

Displays number with no thousand separators.

"Currency"

Displays number with thousand separators, if appropriate; displays two digits to the right of the decimal separator. Output is based on system locale settings.

"Fixed"

Displays at least one digit to the left and two digits to the right of the decimal separator.

"Standard"

Displays number with thousand separators, at least one digit to the left and two digits to the right of the decimal separator.

"Percent"

Displays number multiplied by 100 with a percent sign (%) appended immediately to the right; always displays two digits to the right of the decimal separator.

an

ti

llo n

Spécification Format

Uses standard scientific notation, providing two significant digits.

"Yes/No"

Displays No if number is 0; otherwise, displays Yes.

ch

"Scientific"

"True/False"

Displays False if number is 0; otherwise, displays True.

"On/Off"

Displays Off if number is 0; otherwise, displays On.

É

À finir de traduire et inclure les autres formats quand temps disponible…

Microsoft Power BI / Microsoft Excel BI

692/950

Vincent ISOZ

Exercice 96.: Fonction VALUE Power BI 2018-03

llo n

Pour un premier exemple concret d'introduction à la fonction VALUE( ) se référer à l'exercice de la page 408 (conversion d'une colonne de type texte ayant des valeurs vides et nombres entiers).

É

ch

an

ti

Pour un deuxième exemple concret d'introduction à la fonction VALUE( ) se référer à l'exercice de la page 410 (ajout d'une colonne semestre à la table calendrier).

Microsoft Power BI / Microsoft Excel BI

693/950

Vincent ISOZ

Exercice 97.: Fonction UNICHAR (+VAR) Power BI 2018-03

É

ch

an

ti

llo n

D'abord avant d'énoncer l'objectif qu'on traitera ici, reproduisez l'exercice de la page 677 qui introduisait la fonction LOOKUPVALUE( ):

Microsoft Power BI / Microsoft Excel BI

694/950

Vincent ISOZ

Solution

ch

an

ti

llo n

Une fois la table préparée, ajoutez une nouvelle colonne avec la formule suivante:

É

Il suffit ensuite d'ajouter le résultat dans un page Power BI:

Microsoft Power BI / Microsoft Excel BI

695/950

ti

llo n

Vincent ISOZ

É

ch

an

Notez que fondamentalement l'utilisation de VAR est ici inutile mais c'est juste pour l'esthétique et montrer que l'on peut créer des variables en DAX et les réutiliser par la suite dans une formule.

Microsoft Power BI / Microsoft Excel BI

696/950

Vincent ISOZ

Exercice 98.: Fonction FIND( ) et UPPER( ) Power BI 2018-03

ch

an

Et la table suivante tbl_Clients:

ti

llo n

Considérons à nouveau l'exemple précédent où nous avons une table manuelle de mots-clés comme ci-dessous tbl_MotsCles:

É

Nous souhaitons dans une colonne de la table tbl_Clients indiquer si au moins un mot clé se retrouve dans la colonne Commentaires.

Microsoft Power BI / Microsoft Excel BI

697/950

Vincent ISOZ

Solution

llo n

La solution consiste à écrire la formule suivante non-triviale:

É

ch

an

ti

Ce qui donne:

Microsoft Power BI / Microsoft Excel BI

698/950

Vincent ISOZ

Exercice 99.: Fonction VALUES avec SEARCH( ) et FIRSTNONBLANK( ) Power BI 2018-03

ch

an

Et la table suivante tbl_Clients:

ti

llo n

Considérons à nouveau l'exemple précédent où nous avons une table manuelle de mots-clés comme ci-dessous tbl_MotsCles:

É

Nous souhaitons dans une colonne de la table tbl_Clients indiquer le premier mot-clé trouvé dans la table des mots-clés relativement à la colonne Commentaires! Remarque: À ce jour DAX ne gère par les Wildcards et la fonction SEARCH( ) est aussi à ce jour le seul équivalent de la fonction LIKE que l'on connaît bien en SQL…

Microsoft Power BI / Microsoft Excel BI

699/950

Vincent ISOZ

Solution La solution consiste à écrire la formule suivante non-triviale:

É

ch

an

ti

llo n

Ce qui donne:

Microsoft Power BI / Microsoft Excel BI

700/950

Vincent ISOZ

Exercice 100.: Fonction CONCATENATEX( ) Power BI 2019-07

an

ti

llo n

Considérons la table tbl_Ventes suivante:

É

ch

Et la table suivant ContactClients:

On veut regrouper dans une colonne de ContactClients toutes les dates (dans l'ordre décroissant) pour lesquelles un certain client a fait des commandes (donc toutes les dates d'un client donné doivent se trouver dans une cellule unique!). Microsoft Power BI / Microsoft Excel BI

701/950

Vincent ISOZ

Solution

llo n

La solution consiste en la formule suivante:

É

ch

an

ti

Ce qui donne:

Microsoft Power BI / Microsoft Excel BI

702/950

Vincent ISOZ

DAX Catégorie Finance Exercice 101.: Fonction XIRR (annuel)

an

ti

Considérons la table manuelle suivante:

llo n

Power BI 2019-10

É

ch

Qui dépivotée dans Power Query donne:

Calculer le IRR annuel!

Microsoft Power BI / Microsoft Excel BI

703/950

Vincent ISOZ

an

ti

llo n

Solution

É

ch

Si on veut transfomer cela en IRR mensuel:

Microsoft Power BI / Microsoft Excel BI

704/950

Vincent ISOZ

É

ch

an

ti

llo n

Ou, sous l'hypothèse que chaque année fait exactement 365 jours (…):

Microsoft Power BI / Microsoft Excel BI

705/950

Vincent ISOZ

Exercice 102.: Fonction XNPV Power BI 2019-10

ti

llo n

Considérons la table manuelle suivante:

É

ch

an

Qui dépivotée dans Power Query donne:

Calculer le NPV!

Microsoft Power BI / Microsoft Excel BI

706/950

Vincent ISOZ

É

ch

an

ti

llo n

Solution

Microsoft Power BI / Microsoft Excel BI

707/950

Vincent ISOZ

DAX Patterns Les DAX Patterns sont considérés comme des "très grosses formules DAX" qui sont très très souvent demandées dans la pratique.

ch

an

ti

llo n

Rappelons qu'il y a un site Internet dédié à de tels patterns des créateurs de SQLBI:

É

Et même un livre:

Microsoft Power BI / Microsoft Excel BI

708/950

Vincent ISOZ

Exercice 103.: Quick Measures (mesures rapides) Power BI 2019-01

llo n

Nous allons voici ici un exemple des Quick Measures les plus courantes et les moins triviales (effectivement il n'est pas toujours aisé de devenir comment les utiliser pour avoir un résultat qui a du sens!).

ch

an

ti

Considérons donc un fichier avec le jeu de données suivant (qui nous est bien connu!):

É

Et qui contient des Date de commande toutes continues!:

Microsoft Power BI / Microsoft Excel BI

709/950

llo n

Vincent ISOZ

an

ti

Où nous avons rajouté une colonne Note avec la formule DAX suivante:

É

ch

Allons-y pour étudier les cas majeurs des 27 quick measures actulles (Septembre 2019) de la Quick Measure Gallery:

Microsoft Power BI / Microsoft Excel BI

710/950

ch

an

ti

llo n

Vincent ISOZ

É

Et:

Microsoft Power BI / Microsoft Excel BI

711/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

712/950

Vincent ISOZ

Catégorie Texts Star rating (notation en étoiles)

ch

an

ti

llo n

Créez le tableau suivant:

É

Faites ensuite un clic droit sur Median of Note et choisissez New quick measure:

Microsoft Power BI / Microsoft Excel BI

713/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

714/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

715/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

716/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

717/950

Vincent ISOZ

É

ch

an

ti

llo n

Concatenated list of values (liste concaténée de valeurs)

Microsoft Power BI / Microsoft Excel BI

718/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

719/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

720/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

721/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

722/950

Vincent ISOZ

Catégorie Mathematical operations Multiplication

an

ti

llo n

Considérons la matrice suivante:

ch

Créons une nouvelle mesure d'abord:

É

Et ajoutons une quick measure:

Microsoft Power BI / Microsoft Excel BI

723/950

ti

llo n

Vincent ISOZ

É

ch

an

De type:

Microsoft Power BI / Microsoft Excel BI

724/950

ch

an

ti

llo n

Vincent ISOZ

É

Avec:

Microsoft Power BI / Microsoft Excel BI

725/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

726/950

an

ti

llo n

Vincent ISOZ

É

ch

On obtient alors comme attendu (il faut just renommer la mesure!):

Microsoft Power BI / Microsoft Excel BI

727/950

an

Avec la formule DAX:

ti

llo n

Vincent ISOZ

Correlation (corrélation de Pearson)

ch

On veut savoir par secteur d'activité, la corrélation entre la somme d'argent commandé et la probabilité a priori d'achat.

É

Pour cela, on crée d'abord une mesure en faisant un clic droit sur Secteur d'activité:

Microsoft Power BI / Microsoft Excel BI

728/950

É

ch

On prend:

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

729/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

730/950

ch

an

ti

llo n

Vincent ISOZ

É

On valide par OK et on rajoute la mesure correspondante dans une carte pour obtenir:

Microsoft Power BI / Microsoft Excel BI

731/950

ti

llo n

Vincent ISOZ

É

ch

an

La DAX correspondant ayant été créé est donné par:

Microsoft Power BI / Microsoft Excel BI

732/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

733/950

Vincent ISOZ

Percentage difference (différence en pourcents) Ici on va créer d'abord une mesure de référence:

ch

an

ti

llo n

Ensuite, on crée un quick measure depuis le tableau principal:

É

Et on prend:

Microsoft Power BI / Microsoft Excel BI

734/950

ch

an

ti

llo n

Vincent ISOZ

É

Ensuite on met:

Microsoft Power BI / Microsoft Excel BI

735/950

ch

an

ti

llo n

Vincent ISOZ

É

Et on valide par OK et on ajoute le résultat dans une carte:

Microsoft Power BI / Microsoft Excel BI

736/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

737/950

Vincent ISOZ

Catégorie Totals Running Total (total cumulé)

an

ti

llo n

Pour cette quick measure, on va d'abord préparer la matrice suivante:

É

ch

On ajoute une quick measure sur Prix total avec rabais:

Microsoft Power BI / Microsoft Excel BI

738/950

ti

llo n

Vincent ISOZ

É

ch

an

On prend:

Microsoft Power BI / Microsoft Excel BI

739/950

É

ch

Et on prend:

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

740/950

ch

an

ti

llo n

Vincent ISOZ

É

Et on valide par OK et une fois la matrice triée dans l'ordre alphabétique des Secteur d'activité, on obtient:

Microsoft Power BI / Microsoft Excel BI

741/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

742/950

Vincent ISOZ

Catégorie Time Intelligence Rolling Average (moyenne glissante)

ch

an

ti

llo n

On prépare d'abord la matrice suivante:

É

Et on ajoute une quick measure:

Microsoft Power BI / Microsoft Excel BI

743/950

É

ch

On prend:

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

744/950

ch

an

ti

llo n

Vincent ISOZ

É

Et on met pour avoir une MM6 (attention Microsot compte le zéro comme étant une valeur!):

Microsoft Power BI / Microsoft Excel BI

745/950

É

ch

On valide:

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

746/950

an

ti

llo n

Vincent ISOZ

É

ch

Ce qui donne comme mesure explicite:

Year to Date (année à ce jour) On repart de la même matrice:

Microsoft Power BI / Microsoft Excel BI

747/950

an

ti

llo n

Vincent ISOZ

É

ch

Et on ajoute une quick measure:

Microsoft Power BI / Microsoft Excel BI

748/950

É

ch

On prend:

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

749/950

ch

an

ti

llo n

Vincent ISOZ

É

On prend:

Microsoft Power BI / Microsoft Excel BI

750/950

ch

an

ti

llo n

Vincent ISOZ

É

On valide par OK, ce qui donne:

Microsoft Power BI / Microsoft Excel BI

751/950

an

ti

llo n

Vincent ISOZ

É

ch

Avec la mesure DAX explicite suivante:

Quarter to Date (trimestre à ce jour) On repart de la même matrice:

Microsoft Power BI / Microsoft Excel BI

752/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

753/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

754/950

an

ti

llo n

Vincent ISOZ

É

ch

En ouvrant les trimestres et mois on obtient:

Microsoft Power BI / Microsoft Excel BI

755/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

756/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

757/950

É

ch

an

ti

llo n

Vincent ISOZ

Etc…

Microsoft Power BI / Microsoft Excel BI

758/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

759/950

É

ch

an

ti

llo n

Vincent ISOZ

Month to Date (mois à ce jour) On repart de la même matrice:

Microsoft Power BI / Microsoft Excel BI

760/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

761/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

762/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

763/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

764/950

É

ch

an

ti

llo n

Vincent ISOZ

Et ainsi de suite…

Microsoft Power BI / Microsoft Excel BI

765/950

Vincent ISOZ

Year over Year change (variation d'année en année)

É

ch

an

ti

llo n

Nous préparons la matrice suivante:

Microsoft Power BI / Microsoft Excel BI

766/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

767/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

768/950

ti

llo n

Vincent ISOZ

an

Quarter over Quarter change (variation de trimestre en trimestre)

É

ch

Nous préparons la matrice suivante (avec la date de commande):

Microsoft Power BI / Microsoft Excel BI

769/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

770/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

771/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

772/950

ch

an

ti

llo n

Vincent ISOZ

É

Ce qui donne:

Microsoft Power BI / Microsoft Excel BI

773/950

ch

an

ti

llo n

Vincent ISOZ

É

Qu'il vaut mieux changer sous la forme suivante:

Microsoft Power BI / Microsoft Excel BI

774/950

ch

an

ti

llo n

Vincent ISOZ

Month over Month change (variation de mois en mois)

É

Nous préparons la matrice suivante (avec la date de commande):

Microsoft Power BI / Microsoft Excel BI

775/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

776/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

777/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

778/950

ch

an

ti

llo n

Vincent ISOZ

É

Qu'il vaut mieux changer sous la forme suivante:

Microsoft Power BI / Microsoft Excel BI

779/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

780/950

Vincent ISOZ

Catégorie Aggregate per category Maximum by category (maximum par catégorie)

É

ch

an

ti

llo n

On prépare la matrice suivante:

Microsoft Power BI / Microsoft Excel BI

781/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

782/950

ch

an

ti

llo n

Vincent ISOZ

É

Ce qui donne avec la formule DAX explicite:

Microsoft Power BI / Microsoft Excel BI

783/950

ti

llo n

Vincent ISOZ

Minimum by category (minimum par catégorie)

an

Il suffit de reproduire quasi identiquement l'exemple précédent pour comprendre…! Weighted average (moyenne pondérée)

É

ch

On prépare la matrice suivante:

Microsoft Power BI / Microsoft Excel BI

784/950

an

ti

llo n

Vincent ISOZ

É

ch

On ajoute une quick measure sur le tableau:

Microsoft Power BI / Microsoft Excel BI

785/950

É

ch

Et on prend:

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

786/950

É

ch

Avec:

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

787/950

ch

an

ti

llo n

Vincent ISOZ

É

Ce qui donnera:

Microsoft Power BI / Microsoft Excel BI

788/950

an

ti

llo n

Vincent ISOZ

É

ch

Avec la mesure DAX explicite correspondante:

Variance by caterogy (variance par catégorie) On prépare la matrice suivante:

Microsoft Power BI / Microsoft Excel BI

789/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

790/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

791/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

792/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

793/950

Vincent ISOZ

Catégorie Filters Filtered valued (valeur filtrée) Voici probablement la Quick Measure la plus simple…

llo n

On va voir si on peut recréer avec ce dernier la mesure DAX suivante que nous avions créé plus haut:

ch

an

ti

Pour cela, nous créons une quick measure sur le tableau:

É

Où nous prenons:

Microsoft Power BI / Microsoft Excel BI

794/950

ch

an

ti

llo n

Vincent ISOZ

É

Et dans l'écran qui suit nous mettons les champs et paramètres suivants:

Microsoft Power BI / Microsoft Excel BI

795/950

ch

an

ti

llo n

Vincent ISOZ

É

Nous validons par OK et mettons le tout dans une carte pour obtenir:

Microsoft Power BI / Microsoft Excel BI

796/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

797/950

Vincent ISOZ

É

ch

an

ti

llo n

Difference from filtered value (différence avec valeur filtrée)

Microsoft Power BI / Microsoft Excel BI

798/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

799/950

É

ch

an

ti

llo n

Vincent ISOZ

Et on ajoute le résultat dans une carte:

Microsoft Power BI / Microsoft Excel BI

800/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

801/950

Vincent ISOZ

É

ch

an

ti

llo n

Percentage difference from filtered value (différence avec valeur filtrée)

Microsoft Power BI / Microsoft Excel BI

802/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

803/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

804/950

ch

an

ti

llo n

Vincent ISOZ

Sales new customers (ventes nouveaux clients)

É

On prépare d'abord la matrice suivante:

Microsoft Power BI / Microsoft Excel BI

805/950

ti

llo n

Vincent ISOZ

É

ch

an

Et on ajoute une quick measure à la table principale:

On prend:

Microsoft Power BI / Microsoft Excel BI

806/950

ch

an

ti

llo n

Vincent ISOZ

É

Avec:

Microsoft Power BI / Microsoft Excel BI

807/950

ch

an

ti

llo n

Vincent ISOZ

É

On valide par OK pour obtenir:

Microsoft Power BI / Microsoft Excel BI

808/950

ch

an

ti

llo n

Vincent ISOZ

É

Avec la mesure DAX explicitement donnée par:

Microsoft Power BI / Microsoft Excel BI

809/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

810/950

Vincent ISOZ

Exercice 104.: Capabilité 6 Sigma Excel 2010 / Power BI 2018-03

llo n

Le calcul de la capabilité 6 Sigma n'est pas vraiment un pattern. Toutefois c'est un excellent exemple d'une formule mathématique simple mais qui en DAX peut devenir vite impressionnante.

En plus c'est un excellent exemple d'application de variables VAR (pour simplifier l'écriture de la formule), CALCULATE( ), IFERROR( ), MAXX( ), MINX( ), RELATED( ), FILTER( ), STDEVX.P( ) et DIVIDE( ) en une seule formule.

É

ch

an

ti

Calculon la capabilité des Prix Totaux avec Rabais incluant les Bonus mais que pour les articles de type AST avec les factures payées (à l'état Oui):

Microsoft Power BI / Microsoft Excel BI

811/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

812/950

Vincent ISOZ

Exercice 105.: Moyenne Mobile Excel 2010 / Power BI 2018-03

an

ti

llo n

Considérons la feuille de ventes suivante:

Remarquez que la colonne Date Commande est continue (c'est-à-dire sans trous!).

É

ch

Nous vous demandons de trouver une solution pour calculer une MM-7 (moyenne mobile sur 7 jours).

Microsoft Power BI / Microsoft Excel BI

813/950

Vincent ISOZ

Solution

an

ti

llo n

D'abord nous importons le fichier Excel dans Power BI:

É

ch

Mais nous créons aussi une table de calendrier:

Microsoft Power BI / Microsoft Excel BI

814/950

ch

an

ti

llo n

Vincent ISOZ

É

Une fois ceci fait, nous lions les deux tables sur le champ qui nous intéresse:

Microsoft Power BI / Microsoft Excel BI

815/950

ti

llo n

Vincent ISOZ

É

ch

an

Une fois ceci fait on crée le tableau suivant avec une simple mesure de type somme:

Microsoft Power BI / Microsoft Excel BI

816/950

Vincent ISOZ

ch

an

ti

llo n

Ensuite, on rajoute la mesure finale suivante qui utilise subtilement DATESINPERIOD( ), et LASTDATE( ):

Note: Si la table avec les ventes contient des dates continues, il n'est pas nécessaire de passer par une table des dates!

É

La fin du tableau ressemble alors à:

Microsoft Power BI / Microsoft Excel BI

817/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

818/950

Vincent ISOZ

Exercice 106.: Valeur modale (cas unimodal) Excel 2010 / Power BI 2018-03

ch

an

ti

llo n

Considérons la table suivante:

Nous souhaiterions calculer la valeur modale des Quantité par Article!

É

Étant donné qu'à ce jour et à notre connaissance Power BI n'inclut pas la valeur modale, nous allons devoir passer par un Pattern.

Microsoft Power BI / Microsoft Excel BI

819/950

Vincent ISOZ

Solution

an

ti

llo n

Nous créons une mesure du type suivant qui utilisent subtilement MINX( ), TOPN( ), ADDCOLUMNS( ), VALUES( ), CALCULATE( ) et COUNT( ):

É

ch

Ce qui donnera:

Ce qui peut aider à comprendre, c'est de reproduire le sous-ensemble de la formule ci-dessous qui crée une table:

Microsoft Power BI / Microsoft Excel BI

820/950

ch

an

ti

llo n

Vincent ISOZ

É

Et ensuite la seconde partie:

Microsoft Power BI / Microsoft Excel BI

821/950

llo n

Vincent ISOZ

É

ch

an

ti

La partie subtile est de prendre à la fin MINX( ) !

Microsoft Power BI / Microsoft Excel BI

822/950

Vincent ISOZ

Exercice 107.: Médiane Excel 2010-2013

an

ti

Considérons toujours la même table:

llo n

Sachant que de Excel 2010 à Excel 2013 nous n'avions ni MEDIAN( ), ni MEDIANX( ) et que certaines entreprises on encore Excel 2013 en 2019, nous sommes obligés de laisser cet exercice dans ce support.

É

ch

Nous voulons la médiane par Quantité dans une table.

Microsoft Power BI / Microsoft Excel BI

823/950

Vincent ISOZ

Solution

ch

an

ti

llo n

La pattern DAX à créer est le suivant et remarquez qu'il utilise avec finesse MINX( ), FILTER( ), VALUES( ), CALCULATE( ), COUNT( ) et EARLIER( ):

É

Ce qui donnera:

Microsoft Power BI / Microsoft Excel BI

824/950

Vincent ISOZ

Exercice 108.: Moyenne pondérée Power BI 2018-03

ti

llo n

Considérons la table suivante:

É

ch

an

Essayez de trouver comment nous calculerons la moyenne pondérée.

Microsoft Power BI / Microsoft Excel BI

825/950

Vincent ISOZ

Solution

É

ch

an

ti

Ensuite nous rajoutons la mesure finale:

llo n

Résoudre ce problème se fera en deux étapes car nous allons devoir utiliser une mesure semilocale. D'abord nous créons la mesure suivante:

Microsoft Power BI / Microsoft Excel BI

826/950

Vincent ISOZ

18. Data Optimization

É

ch

an

ti

llo n

Avant toute chose… Rappelez-vous de ne pas charger inutilement dans le modèle Power BI, des requêtes ou tables se trouvant dans Power Query mais que vous n'utilisez pas du tout, ou du moins pas directement (mais indirectement via des transformation intermédiaires!).

Microsoft Power BI / Microsoft Excel BI

827/950

Vincent ISOZ

Désactiver le Auto Date/Time

ch

an

ti

llo n

Pour de multiples raisons que l'on va vous expliquer dans la formation, nous vous conseillons à ce jour de désactiver l'option suivante:

É

Qui fait que dans les formules vous pouvez utiliser un champ automatique du genre: TOTALYTD('xls_Ventes[PrixTotalAvecRabais]','xlsVentes'[DateComma nde].[Date])

Outre les problèmes de performances, on citera (on en a déjà parlé): •

L'impossibilité de personnaliser le calendrier sous-jacent généré automatiquement

Microsoft Power BI / Microsoft Excel BI

828/950

Vincent ISOZ

Chaque colonne à sa propre table de dates, ce qui rend à ce jour impossible de synchroniser de multiples visuels basés sur ces dates!

É

ch

an

ti

llo n

•

Microsoft Power BI / Microsoft Excel BI

829/950

Vincent ISOZ

É

ch

an

ti

llo n

Performance Analyzer

Microsoft Power BI / Microsoft Excel BI

830/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

831/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

832/950

Vincent ISOZ

DAX Studio

an

ti

llo n

On ouvre d'abord le fichier Power BI dont on veut analyser la performance d'une mesure DAX (dans le cas présente, le fichier se nomme DAX_General.pbix). Ensuite, on ouvre DAX Studio:

É

ch

On valide par Connect et on écrit et exécute:

Microsoft Power BI / Microsoft Excel BI

833/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

834/950

llo n

Vincent ISOZ

É

ch

an

ti

On peut voir le code SQL corrrespondant utilisé par SSAS:

Microsoft Power BI / Microsoft Excel BI

835/950

Vincent ISOZ

Utiliser le Query Folding Il semblerait préférable de ne pas utiliser de code M ou de script SQL pour importer des données d’un serveur SQL.

llo n

Il est préférable de construire les requêtes de sorte que toutes les actions pouvant bénéficier du Query Folding (code SQL généré automatiquement par Power Query et envoyé au serveur SQL) soient effectuées en premier puisque lorsqu’une action qui ne peut bénéficier du Query Folding est rencontré, il n’y a plus de Query Folding possible!!

É

ch

an

ti

Pour vérifier que le Query Folding est actif (ou d'un autre point de vue: quand est-ce que celui-ci n'est plus géré), à chaque fois que vous rajoutez des étapes dans Power Query, vérifiquer que vous avez accès à l'option View Native Query et que cette dernière vous affiche du SQL:

Microsoft Power BI / Microsoft Excel BI

836/950

Vincent ISOZ

Identifier les colonnes gourmandes en mémoire (DAX Studio) Le texte ci-dessous est traduit de l'adresse suivante:

llo n

https://www.kasperonbi.com/determine-columns-you-dont-need-using-dmvs-in-power-bi/

ch

an

ti

Ouvrez votre fichier *.pbix et passez à la vue DMX (Dynamic Management Views) et qui vient du monde de Microsoft Analysis Services:

É

Pour comprendre ce qui se passe, j'ai commencé par regarder les tables de stockage. Vous pouvez double-cliquer sur le nom de la requête dans le volet et il génère une requête:

Microsoft Power BI / Microsoft Excel BI

837/950

ti

llo n

Vincent ISOZ

an

Ce dont nous avons besoin ici, c'est de regarder le champ de comptage de lignes et de déterminer lequel est le plus grand. Le nombre de lignes nous donnera le nombre unique de lignes pour une colonne. Les lignes uniques ne se compressent pas bien et sont responsables d’un pourcentage élevé de l’utilisation de la mémoire par le modèle. La bonne chose est que la langue de requête utilisée pour DMV est semblable à SQL. Par conséquent, je peux modifier la requête comme suit: select * from $SYSTEM.DISCOVER_STORAGE_TABLES order by rows_count desc.

É

ch

L'exécution de cette requête me donne le résultat suivant:

Dès que nous repérons le coupable, nous avons une colonne ID dans la table des ventes qui est responsable de la majorité de l’espace nécessaire. Une fois la colonne retirée, la taille du modèle est passée de 156 Mo à 13.6 Mo. Microsoft Power BI / Microsoft Excel BI

838/950

Vincent ISOZ

Data Preview de Power Query

ti

llo n

D'abord au niveau de l'optimisation de Power Query, en allant dans File / Options and Settings / Query Options:

É

ch

an

décocher l'option Allow data preview to download in the background ci-dessous donne parfois des performances significativement meilleures:

Microsoft Power BI / Microsoft Excel BI

839/950

Vincent ISOZ

É

ch

an

ti

llo n

Dans Microsoft Excel, on retrouvera cette option sous la forme:

Microsoft Power BI / Microsoft Excel BI

840/950

Vincent ISOZ

19. Data Viz D'abord rappelons le résumé suivant de la liste en ligne du marketplace de Microsoft concernant les visuels:

ch

an

ti

llo n

https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals

Pour ceux qui veulent savoir faire des graphiques avec R dans Power BI, nous les renvoyons au support de cours disponible ici gratuitement en français:

É

http://www.sciences.ch/dwnldbl/divers/R.pdf

Microsoft Power BI / Microsoft Excel BI

841/950

Vincent ISOZ

Thèmes D'abord un rappel de quelques liens donnés initialement dans la médiagraphie: Galleries de thèmes en ligne:

llo n

https://community.powerbi.com/t5/Themes-Gallery/bd-p/ThemesGallery Lien pour créer des thèmes:

https://powerbi.tips/tools/report-theme-generator-v3/ Liens pour convertir les images en x64 bits pour les fichiers JSON: https://www.base64-image.de/

É

ch

an

ti

Vous trouvez le thème à l'endroit suivant du fichier Zip de votre fichier *.pbix:

Microsoft Power BI / Microsoft Excel BI

842/950

Vincent ISOZ

Rappels importants des pièges dans les fichiers d'exercices De nombreux pièges peuvent vous mener à mal gérer ou comprendre vos visuels. Pour cela, rappelez-vous: Créer une table des dates

•

Mette votre modèle de données à la troisième forme normale

•

Lignes vides

•

Cellules fusionnées

•

Intitulé de colonnes avec retour à la ligne

•

Doublons

•

Liste Microsoft Excel pas sous forme de tableau

•

Colonnes mélangeant textes et nombres

•

Fichier *.csv et *.xlsx des ventes n'ont pas Education écrit de la même manière

•

Certains fichier *.csv ont des données indésirables en fin ou début

an

ti

llo n

•

Pour les Maps, le pare-feu Internet de votre entreprise doit autoriser l'accès aux trois liens suivants: https://dev.virtualearth.net/REST/V1/Locations

ch

https://platform.bing.com/geo/spatial/v1/public/Geodata

É

https://www.bing.com/api/maps/mapcontrol

Microsoft Power BI / Microsoft Excel BI

843/950

Vincent ISOZ

Data Viz Normal (Desktop) screen D'abord un rappel d'un lien pour créer des visuels *.pbviz donné initialement dans la médiagraphie:

llo n

https://charticulator.com/ et pour créer des cartes vectorielles à partir d'images choisies: https://synoptic.design/

Boutons Créer des boutons de navigation Créer des boutons pour ouvrir des liens

ti

Créer des boutons pour effacer tous les filtres

Q&A

an

La liste de mots clés affichée dans cette section n’est pas exhaustive. La meilleure façon de voir si Power BI reconnaît un mot clé consiste à essayer de le taper dans la zone de question. Si le mot ou le terme est grisé, Power BI ne le reconnaît pas. La liste ci-dessous utilise le présent, mais tous les temps sont identifiés dans la plupart des cas. Par exemple, « is » comprend are, was, were, will be, have, has, had, will have, has got, do, does, did. « sort » comprend sorted et sorting. Power BI reconnaît aussi la version au singulier et au pluriel d’un mot et en tient compte.

Mots clés

Agrégats

total, sum, amount, number, quantity, count, average, most, least, fewest, largest, smallest, highest, biggest, maximum, max, greatest, lowest, littlest, minimum, min a, an, the blank, empty, null, prefixed with “non” or “non-“, empty string, empty text, true, t, false, f vs, versus, compared to, compared with and, or, each of, with, versus, &, and, but, nor, along with, in addition to

ch

Catégorie

É

Articles Vides et booléens Comparaisons Conjonctions

Microsoft Power BI / Microsoft Excel BI

844/950

Vincent ISOZ

Mots clés

Contractions

Questions et réponses reconnaît presque toutes les contractions, faites le test. Voici quelques exemples : didn’t, haven’t, he’d, he’s, isn’t, it’s, she’ll, they’d, weren’t, who’s, won’t, wouldn’t Power BI reconnaît la plupart des expressions de date ($$$jour, $$$semaine, $$$mois, $$$année, $$$trimestre, $$$dix dernières années, etc.) et les dates écrites dans différents formats (voir cidessous). Power BI reconnaît également les mots clés suivants : MonthName, Days 1-31, decade. Exemples : January 3rd of 1995, January 3rd 1995, jan 03 1995, 3 Jan 1995, the 3rd of January, January 1995, 1995 January, 1995-01, 01/1995, names of months today, right now, current time, yesterday, tomorrow, the current, next, the coming, last, previous, ago, before now, sooner than, after, later than, from, at, on, from now, after now, in the future, past, last, previous, within, in, over, N days ago, N days from now, next, once, twice. Exemple : count of orders in the past 6 days. in, equal to, =, after, is more than, in, between, before Exemples : Order year is before 2012? Price equals between 10 and 20? Is the age of John greater than 40? Total sales in 200-300? is, equal, equal to, in, of, for, within, is in, is on Exemples : Quels produits sont verts ? Order date equals 2012. Is the age of John 40? Total sales that aren't equal to 200? Order date of 1/1/2016. 10 in price? Green for color? 10 in price? Si une colonne du jeu de données contient la phrase « name » (par exemple EmployeeName), Q&R comprend que les valeurs de cette colonne sont des noms. Vous pouvez donc poser des questions du type « which employees are named robert » (quels employés s’appellent Robert ?). he, him, himself, his, she, herself, her, hers, it, itself, its, they, their, them, themselves, theirs, this, these, that, those sorted, sort by, direction, group, group by, by, show, list, display, give me, name, just, only, arrange, rank, compare, to, with, against, alphabetically, ascending, descending, order greater, more, larger, above, over, >, less, smaller, fewer, below, under, <, at least, no less than, >=, at most, no more than, <=, in, between, in the range of, from, later, earlier, sooner, after, on, at, later than, after, since, starting with, starting from, ending with

Dates relatives

an

Égalité (plage)

Égalité (valeur)

ch

Noms

ti

Dates

llo n

Catégorie

Pronoms

É

Commandes de requête Plage

Microsoft Power BI / Microsoft Excel BI

845/950

Vincent ISOZ

Mots clés

Heure

am, pm, o'clock, noon, midnight, hour, minute, second, hh:mm:ss Exemples : 10 pm, 10:35 pm, 10:35:15 pm, 10 o'clock, noon, midnight, hour, minute, second. (ordre, classement) : top, bottom, highest, lowest, first, last, next, earliest, newest, oldest, latest, most recent, next tous les types d’éléments visuels natifs de Power BI. Si l’option est présente dans le volet visualisations, vous pouvez l’ajouter à votre question. La seule exception à cette règle concerne les éléments visuels personnalisés que vous avez ajoutés manuellement dans le volet de visualisation. Exemple : show districts by month and sales total as bar chart when, where, which, who, whom, how many, how much, how many times, how often, how frequently, amount, number, quantity, how long, what

N premiers Types d’éléments visuels

É

ch

an

ti

Relatifs (relation, qualification)

llo n

Catégorie

Microsoft Power BI / Microsoft Excel BI

846/950

Vincent ISOZ

Slicers (segments)

É

ch

an

ti

llo n

Slicers de dates relatifs

Microsoft Power BI / Microsoft Excel BI

847/950

an

ti

llo n

Vincent ISOZ

É

ch

Trier un slicer (segment) par ordre chronologique des mois

Microsoft Power BI / Microsoft Excel BI

848/950

Vincent ISOZ

É

ch

an

ti

llo n

Trier un slicer (segment) par ordre spécifique

Microsoft Power BI / Microsoft Excel BI

849/950

Vincent ISOZ

Slicer Between de dates, filtré par des dates

É

ch

an

ti

llo n

Masquer/Réafficher un slicer

Microsoft Power BI / Microsoft Excel BI

850/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

851/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

852/950

Vincent ISOZ

Symboles Unicodes Se référer à la page 694.

É

ch

an

ti

llo n

Créer des groupes

Microsoft Power BI / Microsoft Excel BI

853/950

Vincent ISOZ

É

ch

an

ti

llo n

Formatage conditionnel des barres en fonction d'une valeur cible

Microsoft Power BI / Microsoft Excel BI

854/950

Vincent ISOZ

É

ch

an

ti

llo n

Titres dynamiques (avril 2019)

Microsoft Power BI / Microsoft Excel BI

855/950

Vincent ISOZ

Visuel Word Cloud Il ne faut pas oublier dans Power Query pour la source des phrases dans Power Query de: 1. Enlever les doublons! (sinon problèmes!)

llo n

2. Enlever les lignes vides! (sinon problèmes!) 3. Mettre toutes les phrases / mots en minuscules

É

ch

Avec:

an

ti

4. Enlever les retour chariots:

Microsoft Power BI / Microsoft Excel BI

856/950

Vincent ISOZ

Visuel Microsoft Visio

Visuel Synoptic Panel by OKViz

llo n

Comment créer et utiliser une carte personnalisée avec Power BI?

Autrement dit comment créer vos propres cartes, formes dans Power BI avec Synoptic Panel.

Synoptic Panel est un outil de visualisation qu’on peut intégrer dans Power BI et qui permet de connecter des zones dans une image personnalisée avec des attributs dans le modèle de données. Vous pouvez remplir et colorer ces zones dynamiquement et afficher plusieurs informations dessus. Les démarches à suivre pour réaliser ce type de carte!

ti

Exemple sera pris sur la carte de France (suivre les mêmes étapes pour les cartes des autres pays).

É

ch

an

Comme à l'habitude, on va d'abord charger le visuel correspondant dans le Marketplace:

Microsoft Power BI / Microsoft Excel BI

857/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

858/950

Vincent ISOZ

Data Viz Mobile (phone) screen

É

ch

an

ti

llo n

Rappel:

Microsoft Power BI / Microsoft Excel BI

859/950

Vincent ISOZ

É

ch

an

ti

llo n

20. Data Sharing (Power BI Services)

Microsoft Power BI / Microsoft Excel BI

860/950

ti

llo n

Vincent ISOZ

ch

an

Un résumé des différentes options de partage:

Informations:

É

1. Le rôle Lecteur nécessite une licence Power BI Pro ou pour le contenu, d'avoir été publié sur Power PI Premium. Ainsi, si l'espace de travail est dans Power BI Premium, les utilisateurs avec le rôle Lecteur qui n'ont pas de version Pro, pourront quand même lire le contenu. 2. Un double clic sur une tuile d'un dashboard n'active pas, et ne peut activer à ce jour!, le drill-down mais amène juste l'utilisateur vers la page du rapport correspondant.

Microsoft Power BI / Microsoft Excel BI

861/950

Vincent ISOZ

Custom Branding

É

ch

On va dans:

an

ti

llo n

Personnaliser l'interface de Power BI Services:

Et ensuite dans:

Microsoft Power BI / Microsoft Excel BI

862/950

ti

llo n

Vincent ISOZ

É

ch

an

À noter que cela est disponible aussi dans la version gratuite!

Microsoft Power BI / Microsoft Excel BI

863/950

Vincent ISOZ

Dashboard Les dashboards sont faciles à créer, il faut cependant noter qu'à ce jour (fin 2019): 1. Les drill-down ne fonctionne pas sur les tuiles des dashboards

É

ch

an

ti

llo n

2. Les tooltip ne fonctionnent pasr sur les tuiles des dashboards

Microsoft Power BI / Microsoft Excel BI

864/950

Vincent ISOZ

an

ti

llo n

Workspaces App (espaces de travail)

Partager un Workspace App

É

ch

D'abord il faut absolument être dans un Workspace autre que My Workspace! Et être dans un workspace où on a au moins les droits Admin ou Member!

Publier un Workspace App

Microsoft Power BI / Microsoft Excel BI

865/950

Vincent ISOZ

an

ti

llo n

Le bouton se trouve typiquement sur la page d'accueil du Workspace:

É

ch

Notez bien avant de cliquer sur Publish App que:

Nous avons alors:

Microsoft Power BI / Microsoft Excel BI

866/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

867/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

868/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

869/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

870/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

871/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

872/950

an

ti

llo n

Vincent ISOZ

É

ch

Workspaces orphelins

Microsoft Power BI / Microsoft Excel BI

873/950

ch

an

ti

llo n

Vincent ISOZ

É

Notez qu'on peut restaurer les Workspace orphelins:

Microsoft Power BI / Microsoft Excel BI

874/950

ch

an

ti

llo n

Vincent ISOZ

Mais d'où viennent-ils?

É

Eh bien du "simple" fait que que l'administrateur du service Power BI, supprime l'administrateur du Workspace (souvent un simple utilisateur):

Microsoft Power BI / Microsoft Excel BI

875/950

ti

llo n

Vincent ISOZ

É

ch

an

Et on a le même type de résultat lorsque l'administrateur du Workspace est supprimé par l'administrateur du Service via PowerShell.

Microsoft Power BI / Microsoft Excel BI

876/950

Vincent ISOZ

RLS (Row Level Security)

an

ti

llo n

D'abord parlons d'un cas important. Nous créons des groupes pour les rôles et nous souhaitons ajouter une personne externe à notre organisation dans un des groupes. Nous aurons alors le message suivant:

https://portal.azure.com/

É

ch

Il y a deux solutions à ce problème qui n'en est pas un…. La première est officielle. On se connecte avec notre compte Power BI pro au portail Azure:

Microsoft Power BI / Microsoft Excel BI

877/950

ch

an

ti

llo n

Vincent ISOZ

É

On arrive alors sur:

Microsoft Power BI / Microsoft Excel BI

878/950

ch

an

ti

llo n

Vincent ISOZ

É

On va dans les menus suivants:

Microsoft Power BI / Microsoft Excel BI

879/950

an

ti

llo n

Vincent ISOZ

É

ch

Et une fois cliqué sur New guest user, on ajoute l'utilisateur souhaité:

Microsoft Power BI / Microsoft Excel BI

880/950

an

ti

llo n

Vincent ISOZ

É

ch

La personne invitée recevra alors l'e-mail suivant:

Microsoft Power BI / Microsoft Excel BI

881/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

882/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

883/950

Vincent ISOZ

ch

an

ti

llo n

Ensuite, si vous invitiez quelqu'un qui n'a pas la version Pro depuis un compte Pro, ce dernier aura comme message lorsqu'il ouvrira un rapport auquel vous l'aurez invité:

É

Maintenant créons un rôle simple dans Power BI:

Microsoft Power BI / Microsoft Excel BI

884/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

885/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

886/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

887/950

Vincent ISOZ

Partage D'abord un rappel de quelques liens donnés initialement dans la médiagraphie: https://www.powerbitiles.com/?app=PBIRobots

llo n

https://binokula.com/features/ Attention, pour le partage en mode anonyme, il faut: Ne pas avoir utilisé le RLS

•

Ne pas avoir de Live connection

•

Ne pas utiliser un organizational pack

•

Ne pas utiliser de visuels R

É

ch

an

Embed Code

ti

•

Microsoft Power BI / Microsoft Excel BI

888/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

889/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

890/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

891/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

892/950

Vincent ISOZ

É

ch

an

ti

llo n

Intégration dans Microsoft Word

Microsoft Power BI / Microsoft Excel BI

893/950

É

ch

an

ti

llo n

Vincent ISOZ

On fait un clic droit sur un des visuels:

Microsoft Power BI / Microsoft Excel BI

894/950

É

ch

an

Ce qui donnera:

ti

llo n

Vincent ISOZ

Intégration dans Microsoft Excel

Microsoft Power BI / Microsoft Excel BI

895/950

Vincent ISOZ

É

ch

an

ti

llo n

Intégration dans Microsoft PowerPoint

Microsoft Power BI / Microsoft Excel BI

896/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

897/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

898/950

Vincent ISOZ

É

ch

an

ti

llo n

Intégration dans Microsoft Teams

Microsoft Power BI / Microsoft Excel BI

899/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

900/950

ti

llo n

Vincent ISOZ

É

ch

an

Intégration dans Microsoft OneNote App

Microsoft Power BI / Microsoft Excel BI

901/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

902/950

ti

llo n

Vincent ISOZ

É

ch

an

Ouvrir l'App OneNote (et non pas le logiciel OneNote!!!):

Coller le lien qui se transformera alors immédiatement en:

Microsoft Power BI / Microsoft Excel BI

903/950

ti

llo n

Vincent ISOZ

É

ch

an

Cliquer sur le bouton du milieu (flèche de Refresh), de façon à obtenir:

Microsoft Power BI / Microsoft Excel BI

904/950

Vincent ISOZ

Import de fichiers Microsoft Excel Nous verrons ici comment l'utilisation des feuilles de calcul Excel en tant que sources de données fonctionne avec Power BI Services.

ti

llo n

Commençons par une feuille de calcul que nous avons stockée localement sur notre serveur. Le fichier s'appelle ExcelTransactions.xlsx et est stocké localement. Il contient des lignes de données (qui ont été saisies dans la feuille de calcul et ne sont pas des sources de données externes):

É

ch

an

Dans Power BI Service nous cliquons sur:

Microsoft Power BI / Microsoft Excel BI

905/950

ch

an

Prenons l'option:

ti

llo n

Vincent ISOZ

É

Ce qui nous amène sur:

Microsoft Power BI / Microsoft Excel BI

906/950

Vincent ISOZ

llo n

Sélectionnons notre fichier ExcelTransactions.xlsx. Nous allons maintenant voir cette page:

an

ti

Sélectionnon Import Excel data into Power BI:

É

ch

À ce stade, le service n'a pas importé les données en raison du message ci-dessus. Pour mettre en forme un tableau, ouvrons la feuille de calcul et sélectionnez nos données, puis appuyez sur CTRL + T :

Microsoft Power BI / Microsoft Excel BI

907/950

llo n

Vincent ISOZ

ti

Maintenant, répétons le processus de téléchargement de la feuille de calcul Excel sur powerbi.com.

ch

an

Les données sont importées dans PowerBI.com en tant que source de données. Il n'y a aucun rapport de tableaux de bord créés pour cette source de données pour le moment:

É

Nous pouvons créer un rapport de base pour ces données:

Nous pouvons sauvegarder le rapport:

Microsoft Power BI / Microsoft Excel BI

908/950

llo n

Vincent ISOZ

an

ti

Ce qui affiche:

É

ch

Ce rapport apparaîtra maintenant sous Reports, et nous pouvons épingler les visuels aux tableaux de bord:

Ajoutons une nouvelle ligne à nos données Excel! Actualisez Power BI. À ce stade, les données ne sont pas actualisées dans Power BI. La nouvelle ligne que nous avons ajoutée à Excel n'apparaîtra pas dans Power BI. Si nous accédons aux paramètres d'actualisation planifiée dans Power BI, nous voyons ce message:

Microsoft Power BI / Microsoft Excel BI

909/950

Vincent ISOZ

an

ti

llo n

Supprimons cette source de données de Power BI et recommençons. Téléchargez à nouveau les données et sélectionnez Upload your Excel file to Power BI:

É

ch

Nous voyons que ce classeur Excel est maintenant dans Power BI en tant que rapport. Aucun jeu de données n'a été créé:

Microsoft Power BI / Microsoft Excel BI

910/950

llo n

Vincent ISOZ

É

ch

Ce qui ouvre:

an

ti

Nous pouvons également épingler des données aux tableaux de bord:

Ce qui résultera en:

Microsoft Power BI / Microsoft Excel BI

911/950

ti

llo n

Vincent ISOZ

an

Remarque: l'actualisation des données n'est pas prise en charge:

ch

Les feuilles sont en fait modifiables dans Power BI - nous pouvons voir ici que nous pouvons taper dans une nouvelle ligne. Cependant, les données ne sont enregistrées ni dans Power BI ni dans la feuille de calcul Excel d'origine!!!

É

Utilisons maintenant OneDrive Entreprise. Importons la feuille de calcul que nous avons utilisée dans le premier exemple:

Microsoft Power BI / Microsoft Excel BI

912/950

llo n

Vincent ISOZ

ch

an

ti

Dans Power BI, on clique sur Get Data et on sélectionne OneDrive - Business:

É

On se connecte:

Connectez-vous d'abord en sélectionnant Import Excel data into Power BI:

Microsoft Power BI / Microsoft Excel BI

913/950

llo n

Vincent ISOZ

an

ti

Cela crée un Datset mais pas de rapports ni de tableaux de bord:

É

ch

Créons un rapport simple montrant les clients:

Ajoutons maintenant une ligne à la feuille de calcul Excel et vous verrez que cette fois-ci les données du rapport sont actualisées. Microsoft Power BI / Microsoft Excel BI

914/950

Vincent ISOZ

ti

llo n

Ensuite, connectez-vous aux données de Power BI à l'aide de Upload your Excel file to Power BI:

an

Cela crée un rapport, et non un ensemble de données:

ch

Nous pouvons voir pour ce rapport, l'actualisation des données n'est pas prise en charge. Cependant, si nous ajoutons une ligne à notre feuille de calcul dans OneDrive, celle-ci est mise à jour dans Power BI. Il s'agit en fait d'une vue en lecture seule en direct des données de la feuille de calcul OneDrive. Voyons un résumé car c'est vraiment un peu le "foutoir": Mode

Dataset

Report

Refresh

Local File (format data as table)

Import

Oui

Non

Non

Local File (format data as table)

Upload

Non

Oui

Non

OneDrive for Business

Import

Oui

Non

Oui

OneDrive for Business

Upload

Non

Oui

Live

É

Location

Microsoft Power BI / Microsoft Excel BI

Comments

View of spreadsheet

915/950

Vincent ISOZ

Liens avec filtres Attention!!! Les liens avec filtres au jour où nous écrivons ces lignes ne fonctionnent que si et seulement si: Vous travaillez avec un rapport et non un dashboard

•

Vous ne travaillez pas avec Power BI embedded

•

Vous travaillez avec le lien d'origine du rapport et non pas le lien obtenu en passant par Share on the web

•

Ne marche que si les tables et champs n'ont pas d'espaces ni accents!

•

Ne marche qu'avec les champs de type Text

•

Les champs masqués peuvent quand même être utilisé pour le filtrage

ti

Donc on pourra utiliser:

llo n

•

É

ch

an

Qui une fois validé via la touche Enter devient pour la dernière partie:

Microsoft Power BI / Microsoft Excel BI

916/950

Vincent ISOZ

Alertes

É

ch

an

ti

llo n

Les alertes ne marchent actuellement qu'avec trois types de visuels: les cartes, les KPI et les gauges par défaut de Microsoft!!!!!!!!!! Gardez-bien cela en tête!

Microsoft Power BI / Microsoft Excel BI

917/950

Vincent ISOZ

Power BI Embedded Pour la culture générale: https://docs.microsoft.com/en-us/power-bi/developer/embedded-faq

llo n

D'abord le lien majeur:

et:

an

ti

https://microsoft.github.io/PowerBI-JavaScript/demo/v2-demo/index.html

É

ch

https://github.com/microsoft/PowerBI-Developer-Samples

Microsoft Power BI / Microsoft Excel BI

918/950

an

ti

llo n

Vincent ISOZ

É

ch

On peut télécharger la solution .Net et l'ouvrir dans Visual Studio:

Microsoft Power BI / Microsoft Excel BI

919/950

Un exemple fonctionnel:

ti

llo n

Vincent ISOZ

É

ch

an

https://fleetdemoapp.azurewebsites.net/

Microsoft Power BI / Microsoft Excel BI

920/950

Vincent ISOZ

Créer un Tenant Voir la page…

On va sur:

llo n

Obtenir le Client ID (ie Application ID) et Client Secret

https://portal.azure.com/ Et on procède aux étapes suivantes:

ch

an

ti

Via azure cherchez:

É

Vous arrivez alors sur:

Microsoft Power BI / Microsoft Excel BI

921/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

922/950

É

ch

an

Après on va dans:

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

923/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

924/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

925/950

Vincent ISOZ

É

ch

an

ti

llo n

Lorsqu'on lance un application embedded avec cet Application ID on aura:

Obtenir l'ID du rapport Cette étape est facile, il suffit d'ouvrir un rapport dans le navigateur:

Microsoft Power BI / Microsoft Excel BI

926/950

an

Soit en zoomant un peu:

ti

llo n

Vincent ISOZ

Obtenir le Tenant ID

É

ch

On peut l'obtenir via Azure:

Microsoft Power BI / Microsoft Excel BI

927/950

ch

an

ti

llo n

Vincent ISOZ

É

Ou via Office 365 en allant d'abord dans l'administration SharePoint:

Microsoft Power BI / Microsoft Excel BI

928/950

llo n

Vincent ISOZ

É

ch

an

ti

Et ensuite on peut aussi l'obtenir dans les permissions d'Apps:

Et il s'agit du numéro après le "@": Microsoft Power BI / Microsoft Excel BI

929/950

llo n

Vincent ISOZ

Intégration C# .Net

Pour se faire une idée de comment utiliser l'API de Power BI embedded, le meilleur projet fonctionnel à ce jour (Septembre 2019) qu'on peut télécharger et qui fonctionne (!!!), est disponible à l'adresse suivante:

É

ch

an

Merci à Ted Pattison (MVP)!

ti

https://github.com/CriticalPathTraining/PowerBiEmbeddedScratchpad/tree/master/PowerBiEmb eddedScratchpad

Microsoft Power BI / Microsoft Excel BI

930/950

Vincent ISOZ

Javascript Il semblerait que le code suivant pris d'un forum Reddit fonctionne (je n'ai pas testé et de toute façon je n'aime pas la programmation bureautique…): var getAccessToken = function {

llo n

return new Promise(function(resolve, reject) {

var url = 'https://login.microsoftonline.com/common/oauth2/token'; var username = var password = var clientId = Directory - stored

// // // in

Username of PowerBI "pro" account - stored in config Password of PowerBI "pro" account - stored in config Applicaton ID of app registered via Azure Active config

ti

var headers = { 'Content-Type' : 'application/x-www-form-urlencoded' };

an

var formData = { grant_type:'password', client_id: clientId, resource:'https://analysis.windows.net/powerbi/api', scope:'openid', username:username, password:password };

ch

request.post({ url:url, form:formData, headers:headers

}, function(err, result, body) { if(err) return reject(err); var bodyObj = JSON.parse(body); resolve(bodyObj.access_token); }) });

É

}

// ------------------------------------------var getEmbedToken = function(accessToken, groupId, reportId) { return new Promise(function(resolve, reject) {

Microsoft Power BI / Microsoft Excel BI

931/950

Vincent ISOZ

var url = 'https://api.powerbi.com/v1.0/myorg/groups/' + groupId + '/reports/' + reportId + '/GenerateToken';

var formData = { "accessLevel": "View" }; request.post({ url:url, form:formData, headers:headers

an

}

ti

}, function(err, result, body) { if(err) return reject(err); console.log(body) var bodyObj = JSON.parse(body); resolve(bodyObj.token); }) })

llo n

var headers = { 'Content-Type' : 'application/x-www-form-urlencoded', 'Authorization' : 'Bearer ' + accessToken };

Sinon pour ceux qui utilisent Java (bonne chance…!):

É

ch

https://github.com/AzureAD/azure-activedirectory-library-for-java

Microsoft Power BI / Microsoft Excel BI

932/950

Vincent ISOZ

Administration

ch

an

ti

llo n

Dans le About de Power BI Services on peut extraire la version de Power BI Services:

É

Sinon on a au niveau de l'administration:

Microsoft Power BI / Microsoft Excel BI

933/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

934/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

935/950

Vincent ISOZ

É

ch

an

ti

llo n

Monitorer les coûts

Microsoft Power BI / Microsoft Excel BI

936/950

llo n

Vincent ISOZ

On peut obtenir ce numéro ici: https://ea.azure.com/

ch

an

ti

Ici:

É

Ensuite on doit mettre la clé de l'API:

Microsoft Power BI / Microsoft Excel BI

937/950

llo n

Vincent ISOZ

ch

an

ti

Clé qui peut être trouvée ici:

É

On a ensuite accès à de nombreuses tables:

Microsoft Power BI / Microsoft Excel BI

938/950

an

ti

llo n

Vincent ISOZ

É

ch

Et ensuite y'a plus qu'à…

Microsoft Power BI / Microsoft Excel BI

939/950

Vincent ISOZ

É

ch

an

ti

llo n

21. Report Builder

Microsoft Power BI / Microsoft Excel BI

940/950

Vincent ISOZ

Data Munging

É

ch

an

ti

llo n

Microsoft SQL Server Express

Microsoft Power BI / Microsoft Excel BI

941/950

Vincent ISOZ

Microsoft Access

ti

llo n

Une fois Report Builder ouvert, pour se connecter par exemple à une base Microsoft Access, on ira:

É

ch

an

Ensuite, une fois le choix validé, on aura:

Microsoft Power BI / Microsoft Excel BI

942/950

an

ti

llo n

Vincent ISOZ

É

ch

Et on peut au besoin tester la connexion:

Microsoft Power BI / Microsoft Excel BI

943/950

É

ch

an

ti

llo n

Vincent ISOZ

Microsoft Power BI / Microsoft Excel BI

944/950

Vincent ISOZ

22. Intelligence Artificielle et Analyse avancée avec R et Python

llo n

Actuellement environ un quart à un tiers des mes clients utilisent déjà l'intelligence artificielle et l'analyse avancée avec R et Python dans Power BI pour de multiples raison (décisions automatiques, analyses de niveau universitaire, accélération du traitement de données en comparaison à DAX, traitements statistiques de textes). Pour ceux qui veulent en savoir plus sur comment intégrer R avec Power BI, vous pouvez vous référer à l'échantillon de support de cours gratuit suivant d'un peu plus de 2'000 pages:

É

ch

an

ti

https://archive.org/details/Livre_R

Concernant Python, vous trouverez de nombreuses ressources gratuites sur Internet, mais notre support de cours est accessible seulement à ceux qui suivent les formations Python! Microsoft Power BI / Microsoft Excel BI

945/950

Vincent ISOZ

É

ch

an

ti

llo n

23. FAQ (Frequently Asked Questions)

Microsoft Power BI / Microsoft Excel BI

946/950

Vincent ISOZ

24. Assessment (questions de révision)

É

ch

an

ti

llo n

Voici une liste de questions qui peut être utile pour vérifier ses connaissances de culture générale dans Power BI lors de la formation (notez que les réponses sont données dans l'ensemble du livre)!

Microsoft Power BI / Microsoft Excel BI

947/950

Vincent ISOZ

25. Listes des tableaux et figures Tableau 1 Tableau icônes Power Query ..................................................................................... 149 Tableau 2 Tableau icônes DAX .................................................................................................. 396

llo n

Tableau 3Tableau opérateurs DAX ............................................................................................ 403

Figure 1Vue d'ensemble de Power Query ..................................................................................... 26 Figure 2 Types des jointures typiques en SQL et Power Query ................................................... 27 Figure 3 Vue d'ensemble de Power Pivot ..................................................................................... 28 Figure 4Vue d'ensemble de Power View ...................................................................................... 29

ti

Figure 5Vue d'ensemble de Power Map ....................................................................................... 30 Figure 6Vue d'ensemble de Power BI........................................................................................... 31

an

Figure 7Vue d'ensemble de R Shiny ............................................................................................. 33 Figure 8 Types de jointures DAX (source: Curbal) .................................................................... 339 Figure 9 Types de jointures DAX (source: Curbal) .................................................................... 344 Figure 10 Requête DAX dans Microsoft Excel avec commentaires .......................................... 395

É

ch

Figure 11 Requête DAX dans Power BI avec commentaires ..................................................... 395

Microsoft Power BI / Microsoft Excel BI

948/950

Vincent ISOZ

26. Index

llo n

FIRSTNONBLANK( ) .......................................... 663 FORMAT( ) ................................................... 361, 690 FOUND( ) .............................................................. 428 GENERATE( ) ...................................................... 332 GENERATEALL( ) ............................................... 336 GENERATESERIES( ) ................................. 374, 386 GROUPBY( ) ........................................................ 319 HASONEFILTER( ) .............................................. 459 HASONEVALUE( ) ...................... 455, 459, 518, 657 IF( ) ................................................................ 408, 410 IFERROR( )........................................................... 811 IGNORE( ) ............................................................ 321 IN 403, 423 INT( ) ..................................................................... 585 INTERSECT( ) ...................................................... 476 INTERSECTION ( ) .............................................. 380 ISBLANK( ) .......................................................... 661 ISFILTERED ( ) .................................................... 459 ISINSCOPE ( ) ...................................................... 485 ISONORAFTER( ) ................................................ 446 ISSUBTOTAL( ) ................................................... 355 LASTDATE( ) ............................................... 631, 817 LASTNONBLANK( ) ........................................... 665 LEFT( ) .................................................................. 428 LOOKUPVALUE( ) .............................. 677, 682, 686 MAX( ) .................................................................. 525 MAXX( ) ....................................................... 527, 811 MEDIAN( ) ........................................................... 823 MIN( ).................................................................... 520 MINX( ) ......................................... 523, 811, 820, 824 MONTH( ) ............................................................. 578 NATURALINNERJOIN( ) .................................... 339 NATURALLEFTOUTERJOIN( ) ......................... 344 NEXTDAY( ) ................................................ 589, 660 NEXTMONTH( ) .......................................... 593, 660 NEXTQUARTER( ) .............................................. 597 NEXTYEAR( ) .............................................. 601, 660 NORM.INV( ) ....................................................... 388 NORM.S.INV( ) .................................................... 388 NOT ....................................................................... 403 NOW( ) .................................................................. 574 OR( ) .............................................................. 410, 502 PARALLELPERIOD( ) ......................................... 610 PATH( ) ................................................................. 684 PATHITME( ) ....................................................... 686 PERCENTILE.INC( ) ............................................ 555 PERCENTILEX.EXC( ) ........................................ 552 PERCENTILEX.INC( ) ......................................... 551 PREVIOUSDAY( ) ....................................... 589, 660

É

ch

an

ti

ADDCOLUMNS( ) ............... 328, 350, 368, 690, 820 ALL( ).................................... 293, 294, 311, 431, 434 ALLEXCEPT( )..................................................... 453 ALLNOBLANKROW ( ) ...................................... 466 ALLSELECTED( ) ................ 436, 438, 440, 443, 446 AND( ) ........................................... 412, 421, 497, 501 AVERAGE( ) ................................................ 326, 506 AVERAGEX( ) ..................................................... 514 BLANK( ) .............................................................. 657 CALCULATE ( ) ........................................... 476, 480 CALCULATE( ) ............ 419, 535, 581, 811, 820, 824 CALCULATETABLE( ) ............................... 306, 333 CALENDAR( ) ...................................................... 359 CALENDARAUTO( ) .. 363, 589, 593, 598, 601, 607, 610, 620, 626, 632, 640 CLOSINGBLANCEMONTH( ) ............................ 639 CONCATENATEX( ) ........................................... 701 CONTAINSROW( ) .............................................. 423 COUNT( ) .............................................. 533, 820, 824 COUNTA( ) ........................................................... 541 COUNTAX( ) ........................................................ 543 COUNTBLANK ( ) ............................................... 547 COUNTROWS( ) .......................................... 325, 545 COUNTX( ) ........................................................... 539 CROSSJOIN( ) ...................................................... 382 CURRENTGROUP( ) ........................................... 319 DATATABLE( ).................................................... 377 DATE( ) ......................................................... 361, 581 DATEADD( ) ........................................................ 616 DATEDIFF( ) ........................................................ 587 DATESBETWEEN( )............................................ 650 DATESINPERIOD( ) .................................... 654, 817 DATESMTD( ) ...................................................... 626 DATESYTD( ) ...................................................... 626 DAY( ) ........................................................... 580, 690 DISTINCT( ) ......................................... 302, 371, 451 DISTINCTCOUNT( )............................................ 549 DIVIDE( )...................................................... 432, 811 EARLIER( )................................... 667, 671, 679, 824 ENDOFMONTH( )........................................ 639, 654 ENDOFYEAR( ) ................................... 652, 653, 661 EOMONTH( ) ............................................... 361, 579 EVALUATE .......................................................... 284 EXCEPT( ) ............................................................ 443 FILTER( ) ..... 309, 421, 423, 428, 430, 497, 679, 811, 824 FIND( ) .................................................................. 697 FIRSTDATE( ) ...................................................... 631 FIRSTNONBLANK( ) .......................................... 699

Microsoft Power BI / Microsoft Excel BI

949/950

Vincent ISOZ

llo n

SUMMARIZECOLUMNS ( ) ............................... 320 SUMMARIZECOLUMNS( ) ................................ 321 SUMX( ) ........................................................ 493, 497 SWITCH( ) ............................................................ 413 TODAY( ) ..................................................... 574, 585 TOPN( ) ......................... 322, 557, 559, 561, 564, 820 TOTALMTD( ) ............................................. 643, 647 TOTALQTD( ) .............................................. 643, 647 TOTALYTD( ) .............................................. 643, 647 TREATAS ( ) ........................................................ 480 UNICHAR( ) ......................................................... 694 UNION( )............................................... 370, 372, 374 UPPER( ) ............................................................... 697 USERCULTURE( ) ............................................... 406 USERELATIONSHIP( ) ............................... 469, 535 USERNAME( ) ..................................................... 404 USERPRINCIPALNAME( ) ................................. 405 UTCNOW( ) .......................................................... 575 VALUE( ) .............................................. 408, 410, 693 VALUES( ) ............................ 299, 518, 699, 820, 824 VAR............................................................... 694, 811 WEEKDAY( ) ....................................... 361, 576, 690 WEEKNUM( )....................................................... 361 XIRR( ) .................................................................. 703 XNPV( )................................................................. 706 YEAR( )................................................. 361, 577, 690 YEARFRAC( ) ...................................................... 585

É

ch

an

ti

PREVIOUSMONTH( ) ......................... 593, 660, 661 PREVIOUSQUARTER( ) ..................................... 597 PREVIOUSYEAR( ) ..................................... 601, 660 RAND( ) ................................................................ 388 RANDBETWEEN( ) ............................................. 388 RANK.EQ ( ) ......................................................... 324 RANK.EQ( ) .......................................................... 570 RANKX( ) ............................................................. 565 RELATED( ) ................................. 328, 415, 493, 811 RELATEDTABLE( ) .................................... 332, 336 REMOVEFILTER( ) ............................................. 434 RIGHT( ) ............................................................... 429 ROLLUP( ) ............................................................ 352 ROLLUPADDISSUBTOTAL( ) ........................... 357 ROLLUPGROUP( ) .............................................. 353 ROUND( ) ............................................................. 326 ROW( ) .......................................................... 325, 372 SAMEPERIODLASTYEAR( ) ............................. 607 SAMPLE( ) ............................................................ 310 SEARCH( ) ............................................................ 699 SELECTCOLUMNS( ) ................................. 297, 315 SELECTEDVALUE( ) .................................. 473, 683 STARTOFMONTH( ) ........................................... 657 STARTOFYEAR( ) ............................... 652, 653, 661 STDEVX.P( ) ........................................................ 811 SUM( ) ........................... 326, 476, 480, 489, 535, 581 SUMMARIZE( ).................................................... 366

Microsoft Power BI / Microsoft Excel BI

950/950