Évènements Fraîchement Actualisés
Today, I wanted to share with you three tricks that will help you write you DAX formula more quickly
- When you want to refer to a measure, start by [ (bracket). This will filter down the DAX IntelliSense completion list to show all the measures of the model.
- When you want to refer to a table (or a column), start by ‘ (apostrophe). This will filter down the DAX IntelliSense completion list to show all the tables and the columns in the model.
- When you create variables, start their names by _ (underscore). Later in your formula when you want to refer to one of the variables start by _. This will filter down the DAX IntelliSense completion list to show only items that start with a _ (normally the variables you created)
This is all for today.
MCT | MC Power BI Data Analyst
I often go through questions from Internet users and members of the Power BI community and one of the topics that comes up quite often is the correction of DAX metrics that do not work either because they do not return the expected results or because they return an error.
Unfortunately, many people do not explain their problems properly, not to mention that most of them do not share the report, or the data, they are working on. So how do you help these people or in other words how do you debug a DAX measure?
The user was very minimalist. He just indicated that his measure returned the error « Too few arguments have been passed to condition IF » by posting two expressions. A first for a calculated column and a second for a similar calculation but in the form of a measure. Here is if below the expression of the calculated column.
If it is not formatted, It is not DAX
IF('Family'[Product Family]="XXX" || 'Prod'[Team-Prod]="YYY"
AND(Tablename [assignment id],
AND(Tablename [Status ]=CLOSED,
AND(Tablename [Type ]="A",
AND(Tablename [Effort time]<>0,Tablename [ Time]<>BLANK())
Tablename [Effort Time]),
AND(Tablename [Primary Team id],
AND(Tablename [Product Family]<>"XXX",
AND(Tablename [Product Family ]<>"YYY",
AND(Tablename [effort time]<>0,Tablename [Effort time]<>BLANK())
))) ),Tablename [ Effort time]
So, your first reflex is to format the expression and for that nothing is better than the DAX Formatter tool.
In order for the tool to work, the expression to be entered must be entered in the following form:
Name = Formula
- So, I passed the code behind
- Then, I launched the formatting but to no avail.
Next step is to explore the code part by part and for that we must review the syntax of the different functions used and understand the logic of the calculation to be performed.
For the IF function the syntax is as follows:
IF ( Condition , Expression1 , Expression2 )
The condition here is clearly: ‘Family'[Product Family]= »XXX » || ‘Prod'[Team-Prod]= »YYY »
The expression1 is the first AVERAGEX and the second AVERAGEX represents the expression2 and, it is clear that, what is missing at first glance is the comma that allows to pass from the condition to the expression1.
So, I edited the expression, I added the comma and then I launched the formatting again and it worked
Since the tool has applied formatting, this means that on the syntax side, the problem seems to be solved.
To go further, you will find that the application of colors, and the appearance of indents now allows you to better understand the formula and to see that some input errors have crept into the code such as additional spaces or missing quotation marks.
Remember, when it comes to debug a DAX formula, your first step is to format the code and the best tool is DAX Formatter.
This is all for today
MCT | MC Power BI Data Analyst Associate
Since Microsoft Power BI was launched in July 2015, three (3) certifications have been developed to promote and justify skills and mastery of the application.
It seemed important to me to review with you the evolution of the ecosystem of this certification.
The first certification was only born in 2017, two (2) years after the release of the product. Since then, two (2) other certifications have been released, one in 2020 and the latest at the end of February 2022.
These certifications are Microsoft Certified Solution Associate BI Reporting, Microsoft Certified Data Analyst Associate, and Microsoft Certified Power BI Data Analyst Associate, respectively.
In terms of portfolio, area of expertise or solution area, the first certification related to Power BI was part of the “Data Platform” portfolio. When the role-based certifications first appeared, Power BI certification switched to the « Data & AI » competency area. With the release of the latest certification, the area of competencies relating to the Power BI has just changed its portfolio, it is now part of the « Business Applications » solution area.
On the exams side, it was necessary to pass two (2) exams to obtain the MCSA BI Reporting certification, the 70-778 which addressed the Power BI skills and the 70-779 which addressed the Excel skills. Since the Data Analyst certification, candidates need to pass only one exam to obtain the certification. The DA-100 exam was first proposed and since April 01, 2022, the only exam available is the PL-300.
Finally, to prepare for the certification exam, Microsoft offered two (02) official courses of 03 days each, the 20778 and the 20779 which respectively addressed Power BI and Excel. The four (4) days DA-100T00 course was used to prepare for the DA-100 exam. To prepare for the latest Power BI exam, Microsoft offers an official course of three (3) days, the PL-300T00.
I tried to resume all these in one side
This is all for today
MCT | MC Power BI Data Analyst Associate
Yesterday, Monday, February 28, 2022, a new Power BI exam made its debut. Indeed, the PL-300 arrives to replace the DA-100 which will be definitively retired on 03/31/2022.
But what about this new exam and is it much different from the DA-100?
In this first version of the PL-300 exam, we can see that the skills covered are very similar to those of its predecessor the DA-100 and in my opinion there is no significant difference.
By comparing the content of their skills outlines, I found that 95% of the skills covered by the DA-100 are found, in one way or another, in the PL-300.
Some sections of the DA-100 have just been renamed in the PL-300. For example, the section “create a paginated report” has become “choose when to use a paginated report” and “define role-playing dimensions” has become “design and implement role-playing dimensions”.
Some others have been merged. This is the case for the sections “improve cardinality levels by changing data” and “improve cardinality levels through summarization types” which have been grouped under “reduce cardinality levels to improve performance”.
A few sections have been removed and four (04) new topics have been added (or I did not find a match):
- Apply and customize a « report » theme
- Add a Quick Insights result to a dashboard
- Use the Analyze feature in Power BI
- Identify when a gateway is required
For more details, I invite you to consult the comparison of the two Skills outline that I made. https://1drv.ms/x/s!At0U3V37GjTFiBemi-Ln_Cj0MGqf
For more details on the exam please read this article: Exam PL-300: Microsoft Power BI Data Analyst.
MCT | MC Power BI Data Analyst | MCSA BI Reporting
These last days, I had the pleasure to take the DAX Studio for Power BI & SSAS online structured course provided by Enterprise DNA Ltd.
About DAX Studio
For the people who don’t know yet about DAX Studio, it is simply a free Power BI « amazing » external tool provide by SQL BI you can use it to write, execute, and analyze DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular.
About the course
Before starting the course, I thought that I’m pretty good with DAX Studio just because I was able to create and test some DAX measures using the tool. In fact, I was using only 2 « DAX Studio » keywords which are DEFINE and EVALUATE and when I took the course I understood that DAX Studio is much more.
The course is presented by Antriksh Sharma in a 100% video format. The course is divided into 7 parts (see bellow). It starts at a beginner level and ends with advanced notions.
- Installation and Data Model Introduction
- Guided Tour of DAX Studio
- Querying Tabular Model with DAX Studio
- Additional Topics
- Loading Performance Analyzers Data
- Query Builder
- Dynamic Management Views
Taking the course, I discovered and learned a lot about DAX Studio tool and its capabilities and encourage any one who want to move a step further in his DAX journey to take that course.
Have a nice day
MCT | Microsoft Certified Power BI Data Analyst Associate | MCSA BI Reporting
This week a community member posted a question on the Power BI Exchange User group. His question was about some unexpected result returned by a DAX measure.
The unexpected results were « NaN » and « Infinity« . So, why did he get these results?
Investigating the problem
The user created a calculated column in the « Export_Data » table that return a ratio by dividing the values of the « Sales » column from the « Export_Data » table by the values of the « Year » column from the « Users » and he provided the formula he was using and like you can see there is noting special with it:
Column = ‘Export_Data'[Sales] / RELATED ( ‘Users'[Year] )
Because he provided only screenshots with most of the data grayed in black and not visible, I first needed to understand the data and reproduce them the most accurately possible to figure out what can provide these results. After some minutes, I built a small model with two tables « Users » and « Export_Data » with a one-to-many relationships.
And I added the calculated column using the formula he provides, and surprise! everything was working fine. No « NaN » and no « Infinity » results?!
So, I decided to add more data in both tables with nulls, zero and non related items to check what I was pretty sure of it.
And that confirmed me what was the problem.
- The RELATED function work like a lookup function and return blank when it does not find a matching value in the lookup table.
- When you divide a number that is different from zero by noting you get « Infinity« .
- When you divide zero by noting (or zero) you get « NaN » which means « Not a Number«
How to avoid this result?
In DAX a best practice when creating ratios is to use the DIVIDE function instead of the « / » operator because it can return blank() or an alternate result on division by 0.
I created a second calculated column « Ratio » that use the DIVIDE function and as you can see instead of NaN and Infinity result it return (blank)
Yesterday, Zoe Douglas, a Senior Program Manager for Microsoft Power BI, presented a wonderful session at the Entreprise DNA Power BI Summit 2022. Its title was « FROM SOURCE TO INSIGHTS –
End to End Presentation of Creating a Power BI Report« . The entire session was amazing with so many « little but useful » tips that can make your Power BI report DEV much easier and more productive.
About the session
During this session Zoe presented a pipeline for the development of Power BI reports highlighting collaboration through Microsoft Teams. So, if you are collaborating with Teams, this is right for you.
About the tips I learned
Frankly, I knew some of the tips she used but didn’t realize their potential until she put her finger on them and used them in a concrete case. Here are some of the ones I really liked
- Applying a custom format to a measure directly from the ribbon.
- Using tabular Editor to create basic measures for any numeric column in you model.
- Using the Quick Measure feature to create intermediate and advanced.
- Using « Shift + Arrow keys » to move the visuals by 10 px.
- The replay will stay on YouTube for non eDNA member only for 3 days. End to End Presentation of Creating a Power BI Report | Enterprise Analytics Summit Session 11 – YouTube
- You can find event details here Enterprise Analytics Summit – February 2022 (enterprisedna.co)
- If you are an eDNA member, you will be able to access the resources here : Enterprise Analytics Summit – Free | Enterprise DNA
A BIG thanks to Zoe for sharing her knowledges and all my thanks to Enterprise DNA for providing such as events « for free »
Salut à vous
Après un petit break, l’équipe Power BI est de retour ce mois de février avec beaucoup de nouveautés.
Mais avant d’aller plus loin, voici un rappel très important. À compter de ce mois de février 22 il faudra impérativement avoir installer WebView2 qui devient un prérequis. Donc si vous rencontrez des problèmes avec la mise à jour de Power BI Desktop, commencez par explorer cela. Pour plus d’informations, voici un lien vers l’article qui annonce cela Reminder: WebView2 will be required for the next Power BI Desktop release | Microsoft Power BI Blog | Microsoft Power BI
Côté nouveautés, voici ce qui a retenu mon attention
- Tout d’abord ce qui fait le buzz à savoir la prise en charge de plus de sources de données (y compris SQL Server, Azure SQL DB et Synapse) avec des paramètres M dynamiques.
- Le visuel personnalisé « Charticulator » est maintenant certifié
- Le nouveau volet de mise en forme des visuels est désormais activé par défaut pour les utilisateurs effectuant une mise à niveau vers la version de février.
- De nouvelles options de mise en forme des visuels sur la vue mobile permettent maintenant de dissocier l’apparence d’un visuel entre la vue normale et la vue mobile.
- Un nouveau modèle d’application qui décrit les nouvelles fonctionnalités publiées dans Power BI que vous pouvez consulter en ligne avec une licence Pro !!!
Liens utiles :
- Power BI February 2022 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI, et la video Power BI Update – February 2022 – YouTube
- La revue de Ruth Pozuelo Martinez
Dark mode, Goals for free and a rant | Power BI Update Feb 2022 – YouTube
- Article de Chris Webb sur les paramètres M dynamiques : Chris Webb’s BI Blog: TSQL Table-Valued Functions And Dynamic M Parameters In Power BI DirectQuery Chris Webb’s BI Blog (crossjoin.co.uk)
Les certifications spécialisées et basées sur les rôles Microsoft expirent chaque année, sauf si elles sont renouvelées. La bonne nouvelle c’est que le renouvellement est gratuit
Afin de renouveler votre certification, vous devez :
- Connectez votre profil d’apprentissage « Microsoft Learn » avec votre profil de certification « Microsoft Dashboard »
- Préparer l’évaluation
- Réussir l’évaluation avant l’expiration de votre certification.
Ce qui est aussi bon à savoir c’est que :
- La fenêtre pour passer l’évaluation est de 06 mois. Elle 06 mois avant la date d’expiration de votre certification.
- Il est possible de passer l’évaluation autant de fois que nécessaire avant l’expiration du délai. Toutefois après la deuxième tentative, il vous faudra attendre 24 heures avant de pouvoir repasser l’évaluation.
Pour de plus amples informations, visitez la page sur le site Microsoft : https://aka.ms/RenewYourCert
#StayCertified #Microsoft #Certification
Début janvier 2022, l’équipe Microsoft a annoncé que la certification « Microsoft Certified : Data Analyst Associate » allait être renommé et il sera maintenant question de « Microsoft Certified : Power BI Data Analyst Associate »
Il est à noter que ce changement entrera en vigueur le 28 février 2022.
Microsoft a aussi annoncé qu’un nouvel examen de certification sera introduit le même jour le « PL-300 : Power BI Data Analyst ». Cet examen remplacera l’actuel « DA-100 Analyzing Data with Microsoft Power BI » qu’il est prévu de retirer le 31 mars 2022.
Qu’est-ce que cela implique ?
Pour les personnes qui sont déjà certifiées
RIEN, leurs « Transcript » sera automatiquement mis à jour et le nouveau nom apparaîtra à la place de l’ancien.
Pour les personnes qui prépare leur examen de certification
- Jusqu’au 31/03/2022, ils peuvent passer l’examen DA-100.
- À partir du 28/02/2022, ils pourront passer l’examen PL-300
- A partir du 01/04/2022, il ne sera possible que de passer l’examen PL-300
Quel examen choisir entre le 28/02 et le 31/03 ?
Bien que la fiche de préparation de l’examen PL-300 ne soit pas encore disponible, Microsoft indique que les deux examens couvrent le même domaine de connaissances mais bien qu’ils soient très similaire Microsoft recommande de se concentrer sur le PL-300.