Power BI 2022

 DAX Data Analysis Expressions VARIABLES Running Total = VAR MaxDateInFilterContext = MAX ( Dates [ Date ] ) VAR MaxYear = YEAR ( MaxDateInFilterContext ) VAR DatesLessThanMaxDate = FILTER ( ALL ( Dates [ Date ] , Dates [ Calendar Year Number ] ) , Dates [ Date ] <= MaxDateInFilterContext && Dates [ Calendar Year Number ] = MaxYear ) VAR Result = CALCULATE ( [ Total Sales ] , DatesLessThanMaxDate ) RETURN Result FORMAT Numbers actual = if(sum[actual] >1000000, "FORMAT(SUM([actual], "#, ##M"), IF(SUM([actual]>=1000, "FORMAT(SUM(actual]), "#,,.0K")) FORMAT(min(column, "0.0%") FORMAT(min(column, "Percent") eg. if matrix is filtered,  IF(ISFILTERED(field], SELECTEDVALUE([column]) HASONEVALUE  Valuecheck = if(HASONEVALUE([column], VALUES(field)) FILTER table by related field = united states and sumx salesamount_usd = SUMX ( FI...

So it looks like the future of Power BI development is in the Power BI service and that the Power BI desktop may become redundant. To move development in that direction, here are some of the key steps: 1. Attempt to create one source of the truth, using Data Flows . With Data Flows, you create containers for your tables that you will re-use for to create your data sets and Power BI Reports (but see below, you should try to move to maybe 1 or just a few data sets). Note: Queries can still be used by creating in Power BI desktop and copying the M code to a query within a Data Flow. Multiple data flows help organise the tables into manageable spaces. I'm using groupings of 4 and alphabetic ranges maybe a good way of assigning the tables. Data Flows are created in Workspaces (not available in 'My workspace'. Learn More about Data Flows 2. Use the Power Query Editor within the Power BI Service , so your data preparation can be shared with others in the organisation a...

As Power BI moves away from Power BI desktop and towards the service side, its going to be important to make sure you develop your knowledge of M code (IMO). This means using m code for transforming data in the power service power query editor and not using DAX in power BI desktop to create columns as well as not using the power BI query editor at all (except for grabbing M Code from Native SQL queries to place in Power BI service data flow queries). Below are two examples of using M code in power query when adding custom columns in Power Query. The first, adds a custom column 'DayofWeekName' and the second adds a custom column OrderDateWeekNum when use the [OrderDate] field from the FactInternetSales table of the adventure works 2019 database. The code is added to Power Query in the Power Service on tables within dataflows (you can use it in PBI desktop, but we're trying to move away from that). The tables within the data flows are then pulled into power bi, with no more w...

So for full testing of Power BI, you can setup a Microsoft Azure SQL server database using the Adventure Works DW 2019 sample. This will allow you to connect from the Power BI service as it cannot connect easily to your laptop (you would need to configure a gateway etc). So, go to azure.com to create an azure account (with an outlook/hotmail address). The free account gives you $200 of free credit and 12 months access to popular services. Here you can get a low cost cloud SQL server, which Power BI service can connect to without configuring a gateway and also you don't need to worry about losing it etc. When you have a subscription, the first thing to do is to create a resource group which is basically a folder for related resources. Tip: You cannot rename a resource group. You don't have to create a resource group, but if you are going to create a lot of things, then its probably worthwhile getting organised. Inside the resource group (or outside if you didn't create one)....

Thinking: Data Source (e.g. Data-warehouse) Data Flow (re-usable data in Power BI Service pulled from the data-source) Power Query in Power BI Service for Data Flows (central data transformation point) Data Set (data pulled from Data Flow in Power BI Desktop) Data model (model in Power BI Desktop) Report (Report published from Power BI Desktop to Power BI Service). Challenges - Data Flows pull tables by default, which cannot be related in the Data Flow. You can however pull a query into a data flow. So how far can this go to full data centralisation in Power BI service > Data Marts! Introduction   OK, so Power BI data flows are all about creating re-usable data and having the Power Query Editor within the Power BI service, rather than having to use it in Power BI. The benefits are therefore: 1. Having data that can be re-used would also be excellent. It can be refreshed once (or up to 8 times a a day with Power BI Pro license or up to 48 times a day with Power BI Premium license...