The Algebra Teacher Powers to B.I.

 In this blog, I want to dive into Power Automate.  When I first started with the Power Platform I dove deep into Power BI which then made way for Power Apps.  Soon thereafter I was introduced to Power Automate.  I sure wish someone had told me about Power Automate when I was a teacher of middle schoolers.  There were so many mundane daily and weekly tasks that I could have easily automated if I just would have been exposed to the program.   Power Automate is great for taking those routine or repetitive tasks and making them no longer a part of your daily routine. Sure setting up the Power Automate flow takes more time to get created, but once created you then can devote all that extra time you normally would have devoted to the task and now spend them on more mission-critical issues for your job.   In this video, I want to show you how you can send an automated email with just a click of a button.  Since I learned Power Automate after lea...

 In this blog, I am excited to release my 2nd episode in my new series covering the Power Platform.  If you have ever created Canvas Apps and have not used Global Variables, you will want to keep reading.  Global variables allow you as the app developer to design your application with fewer lines of code and in some cases avoid delegation warnings.   Global variables have many use case scenarios because you can call upon a global variable within any screen or control in your application.  For this video, I explore two use case scenarios for Global variables.  The first is to store custom colors that you want to re-use during your app design.  The second is able to retrieve information like email, display name, manager's information, etc. about the current user who is utilizing your application. Check out this video and see how easy it is to declare Global Variables in canvas applications. If you enjoy this video and are interested in formal traini...

 It’s a New Year and time to get back to making more videos to help anyone who wants to learn about the various Microsoft products we here at Pragmatic Works train.  I’ve made over 40 different YouTube videos over the past two years, but for this year I wanted to create a structured series that will be dedicated to the Microsoft Power Platform.    Each month I plan on posting at least two videos that will cover various topics dedicated to Power Apps, Power BI, Power Automate, and Power Virtual Agents.  Some of the videos will be “one-off” videos while the majority will be continuations of discussion from the prior videos in the series.   I plan on giving you all the resources you will need to follow along.  Whether you start with me from episode 1 or you pick up with me on episode 11, I hope to give you great insights into how to use the programs and start implementing your business solutions.   In this first episode, I am starting wit...

 Bookmarks have been around for quite some time in Power BI, but with the new November update, you now have some extra functionality to make report creating easier. In this video, learn how to create bookmarks in Power BI using the newest bookmark update that came with the November 2021 edition of Power BI. With this new update, you now can insert a bookmark navigator on your report pages. This feature helps the cumbersome old process of adding separate buttons for each bookmark and attaching actions to them. You can use this new feature to update old reports or create new reports with bookmarks.   If you enjoy this video and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Matt20" at check out when purchasing our On-Demand Learning classes to get an extra 20% off - https://pragmaticworks.com/pricing/

 When first starting with Power BI and diving into the Power Query editor you start to realize there are many ways to accomplish the same end goal.  For example, if you want to remove columns you can right-click on a column and remove it, hit the remove columns icon from the home ribbon, or even remove other columns.  The decision you make to remove the columns though has a consequence.  For example, if you use the remove column feature this makes it harder to modify this step in your applied steps.  If you chose the remove other columns feature or what I will showcase in my video below, the Choose Columns feature, then you can easily modify the applied step.  I believe the Choose columns step is the easiest of them all to utilize when removing columns.   Have you ever had a query with a copious amount of columns and you want to quickly go to a column without having to scroll through the whole query?  Well, I have the solution for you. In the ...

 Do you need to bring data from multiple files into one table because the column structures, data types, etc. are similar?  Learn how to quickly do this in the video below.   In this video, I showcase how to bring in two different data sources that contain similar data and append/combine them into one table. This is useful to the user so you don’t have to go to the original data source and duplicate data to get what you need all in one table. Also, addressed is the scenario of what if I have different names for my columns or extra columns in one dataset compared to the other. I use some play educational data, but hopefully, you will see how to apply this to your business scenario. 👍   If you enjoy this video and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Matt20" at check out when purchasing our On-Demand Learning classes to get an extra 20% off - https://pragmaticworks.com/pricing/

Learn how to group and bin your field values for use in visualizations. This trick can help save space in your data model and never use a DAX function in order to make a calculated column that categorizes or groups your data using IF or SWITCH functions. This is great for new users to Power BI as well as save time for experienced users. In this video, I show how to group the sales territory countries into their corresponding continents. I also showcase how to group the ages of customers into different age ranges. 👍If you enjoy this video and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Matt20" at check out when purchasing our On-Demand Learning classes to get extra 20% off - https://pragmaticworks.com/pricing/

  DAX has two very similar functions, Related and RelatedTable which accomplish two very different actions. We use these functions when we need to communicate to another table that we are not currently working on. These are quite often used in calculated columns and aggregations. In this video, I show the difference between the two and hope to clear up any confusion you may have had in the past. Also, you will see some common use case scenarios of where these are used in Power BI Development. Bonus DAX functions thrown in will be SumX, AverageX, and RankX.

In my last blog post, I talked about how to start using Power BI's Artificial Intelligence capabilities in the Power Query Editor.  We looked at using the Add Column From Examples feature, which can be a huge time saver. In this blog, I want to showcase how to use the Q&A visual. You can utilize this visual as a report builder or empower your data consumers to ask their own questions about your data to gain further insights. The Q&A visual is always getting new updates with the monthly Power BI updates so this is by no means where Q&A will stop in Power BI. Check out the demonstration below https://www.youtube.com/watch?v=f7OUHm-pWns

 Power BI has a multitude of Artificial Intelligence capabilities.  I wanted to start a YouTube series highlighting some of these capabilities you can take advantage of starting today. In this first episode, I want to showcase how you can add columns in the Power Query Editor without once writing a single formula using the M language.  Might it be more braggadocious to execute these columns using formulas and showcasing them? Sure.  But my motto has always been "Work Smarter Not Harder." Check out this first video in my series on how you can use Artificial Intelligence in Power BI.

 One of the most powerful features of Power BI when it comes to DAX is a time series analysis.  Using pre-defined DAX functions can allow you to report on values based on specific time periods.  You can do things like referencing the previous year’s value, have a running year-to-date total, find closing balances for the month, and much much more.   For this blog, I want to explain how to get a rolling total for a specific time period.   Rather than just simply writing the expression, I am going to break it down step by step of how it is built out and why it works.   By understanding the reasoning behind the formulas, you can leverage this knowledge to apply time series analysis into your own Power BI reports.   For our rolling total, I will find the rolling sales amount for the last 3 months no matter what level of dates I am currently viewing in a visualization.   This calculation can be easily modified to finding a rolling total for the ...

If you have not taken advantage of the new modern visual tooltip preview feature in your Power BI reports yet you want to take advantage now.   Below is why you want to take advantage of this feature.   With the new tooltip, if any of your visuals have a dedicated drill through page attached to it, the tooltip will display this feature.   Before this, your end-user needed to know to right-click a data point to access your drill through page or you had to a separate button to your report page the lit up after a visual selection attached to a drill through was made.   Now, it is way more intuitive to all users that drill-throughs have been created.   Also, with the new tooltip feature if you visual has drill down capabilities they can select to drill down right from the tooltip and not click on the dedicated buttons attached to the visual.   The styling of the tooltip has also been updated which I believe is a cleaner look for end users.   Yo...

 Whether you are new to Power BI or have been using it for a while, filter context for visualizations and measures are so important to understand.  I will admit it took me a while as someone brand new to Power BI and data modeling to wrap my head around it.  With some practice and learning from others, things finally clicked for me.  So for this week’s video, I wanted to showcase to you the basics of filter context.  After understanding how filter context worked, I then realized that sometimes I didn’t want the current filter context or I wanted to modify it to show values that were important to me.  This then led me down the rabbit hole of the CALCULATE function in DAX.   Wow!  I did not even know how powerful the CALCULATE function is in terms of modifying filter context.   Check out this week’s video for an explanation of filter context and then how you can modify it using the CALCULATE function.   I also showcase three other...

    Have you used the personalize visuals feature yet in Power BI?   If not, then you could be missing out.   The Personalize Visuals feature allows anyone you are sharing your with after published to the Power BI service to make changes to your visuals.   Have no fear though!   When they change the visuals it will not override your original report.   This feature is great if you have ever had to respond to a request from your shared users that they would like the report to be modified to their liking.   With this simple feature, the end-user can change the visualization type, change what measures or fields that are brought into the visual, and then simply make a personal bookmark after they make the changes.   Again, this will not change the initial report you published and that others have access to.   This will only make a new view of the report for the user who personalized the visual.   If you decide to use this feature...

  Power BI came out with a new preview feature in the February 2021 version of the Power BI Desktop known as small multiples.   To use this feature as of the May 2021 release, you need to turn on this feature in your preview features.   The small multiples feature allows you to take a bar/column, line, area, or combo chart visual and essentially duplicate the visuals on the page with each one being uniquely filtered down by a specific dimension.   For example, you could split a column chart that displays your sales by country into unique duplicates that report for each year.   To create the small multiples you will drag the field that you want to dissect your original visual by into the Small Multiples field in your visualization pane.   After seeing the new result, if you want to have even more control of how the visual is display check out the Small Multiple Title section and Grid Layout section in the formatting area of the visualizations pane. ...

In the Power BI Desktop, you are allowed only 1 active relationship between tables in your data model.  Many times this is all that is needed.   There are, however, scenarios where this becomes a huge reporting issue.   For example, what if you have a table of sales where one column contains the date a sale occurs and another column that records the date the money from the sale is collected.   Well, depending upon which relationship is active with your Date table in your model the sum of your sales will either be based on the sale date or the money collected date.   If you need to see both values though this is where you need to make a separate measure using the function USERELATIONSHIP paired with the function CALCULATE.   The USERELATIONSHIP function will allow you to access inactive relationships in your data model.     Another scenario, where I have seen this be an issue, is when users need to see their sales based on the city where t...

 Slicers are great in Power BI reports because they allow for the end-users to see the data filtered in any way they see fit..  What becomes a nuisance, however, is when the end-user wants to see the report page back to its unfiltered state.  For each slicer selection that becomes a click in order to reset the filter. In this video, I show how to quickly set up a bookmark page and insert a shape onto your report to activate your bookmark which will reset your filters with one single click.  There are other ways to accomplish this on the Power BI service, but if you have an inexperienced user they may not know where to go to reset the page to its default view.  With the addition of a button or shape and utilizing the bookmark, there is no way they can't figure it out now.

  Another month and another Power BI Desktop update.   What does April 2021 have in store? Keep reading.   In the April 2021 edition, there are quite a few features I want to highlight for you.   The most exciting feature in my opinion is the new custom visual dedicated to Power Automate.   With this visual, you can kick off a Power Automate with a click of a button directly from your report page.   There are a few templates already in the visual, but Microsoft plans on releasing many more shortly.   You can also create a custom flow and not start with a template.   The Small Multiples preview feature has some updates when it comes to the padding of the visual as well as the ability to access Small Multiples in the combo chart visuals.   The shapes feature has had a complete overhaul.   Instead of the standard 5 shapes that were available in the Power BI Desktop previously, with this release, there are now 21 shapes to choose...

  Organization for me is always a priority.   When things are not in the right place and I can’t find something quickly I get frustrated.   This is not only in my daily home life when trying to find a tiny car that my son remembers from 2 weeks ago and he now has to have to continue playing, but also in my Power BI report building.   When building DAX measures on a report, some users think the measure has to be on the table they are referencing.   The fact of the matter is a DAX measure can be on any table no matter the reference and it will still run properly.   Due to this, I like to put all of my measures on a dedicated table so they are easy to find for me and my end users.   This table is not a data source table, but one I create within the report itself.   You might be asking will this extra table affect the performance of my report or the data refresh.   It will not!   Let me walk you through the process of how easy it easy to...

  The IF function in DAX is a very popular logical function.  It will perform a logical check on a value expression and produce a value of True or False.  Based on the resulting True or False you can then decide what value you want to be reported.  Your value can be a hardcoded string value or some other calculation.  When you only have two outcomes to choose from the IF statement is very straightforward.  But what if you have 3 or more outcomes.  Then you will need to use more than one IF statement.  Instead of reporting a value or calculation for your first false result, you will code in another IF statement to run.  The more outcomes you need the more IF statements you will use.  This can be tiring to code and hard to read.  Here is where the SWITCH function comes into play.   The SWITCH function allows for an easier-to-read function compared to nested IF statements. The SWITCH function can always be used in place of the IF ...