Keyboard shortcuts is an interesting topic for developes that can really improve your report development in Power BI Desktop. In this post I show you some keyboard shortcuts/hotkeys when writing DAX in Power BI Desktop.
Indend right: Ctrl + ]
Indent left: Ctrl + [
Mini-tip: You can also indent your code to ther right by pressing TAB or indent left by pressing Shift + TAB. But, the difference is that if your cursor is in the middle of a line, when you press TAB it divides your code to two pieces and indends the characters to the right from the position that cursor is in.
New line keep indent: Shift + Enter
New line starting from first of line: Alt + Enter
Activate Intellicence: Ctrl + Space
Comment multiple lines: Ctrl + KC or Ctrl + /
Uncomment multiple lines: Ctrl + KU or Ctrl + /
Move the current line up/down: Alt + Up/Down Arrow Key
Enter multiple lines of code at once: Ctrl + Alt + Up/Down Arrow Key
Find and replace a word: Ctrl + D to highlight the current word, Ctrl + D again to find/highligh the same next word. Continue pressing Ctrl + D to find/highlight all same words, then start typing to replace all words at once
Find and replace all of a kind at once: Ctrl + Shift + L to highlight a part of your DAX expression then start typing to replace the highlighted words at once
If you are a SQL guy I bet you’ve used “IN” operator zillions of times. You might also looked for the same functionality in DAX and I’m sure you’ve found fantastic blog posts showing you how to mimic the same functionality in DAX. The October release of Power BI Desktop is full of new analytics features such as Grouping, Binning and TOPN filtering. On top of that, one new awesome feature that is not documented at time of writing this article, or at least I haven’t find anything over the internet, is “IN” operator in DAX. In this post I show you how to use it in your DAX expressions.
The latest version of Power BI Desktop (Current version is: 2.40.4554.463 64-bit (October 2016))
Note 1:You need to install SSMS2016 to be able to write DAX queries provided in this article. Alternatively, you can use DAX Studio . If for any reasons you cannot use SSMS 2016 or DAX Studio and you only have Power BI Desktop, don’t worry, I’ll provide some examples in Power BI Desktop as well.
Note 2: If you run previous versions of SQL Server it’s absolutely alright. There is nothing special in AdventureWorksDW2016CTP3 for this article that you don’t get in older versions of the sample database. But, keep in mind that SQL Server 2016 Developer Edition is now free and you can download it very easily. Check this out if you’re interested to see how.
After downloading the latest version of Power BI Desktop run it then
“Get Data” from SQL Server
From AdventureWorksDW2016CTP3 load “FactResellerSales”, “DimProduct”, “DimProductCategory”, “DimProductSubCategory” and “DimDate” to Power BI Desktop model
Find the local port of Power BI Desktop by opening “msmdsrv.port.txt” file from the following path:
“%UserProfile%\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspaceXXXXXXXX\Data”
Note:The “XXXXXXXX” postfix is a random number.
Open SSMS 2016 and connect to Power BI Desktop model as an Analysis Services local server. Do you want to learn more about how to connect your Power BI Desktop model from different software? Then check this out.
Now we want to filter “CalendarYear” so that the query shows sales values for 2011 and 2012 only. One common scenario we had to do in prior versions of Power BI Desktop, Power Pivot or SSAS Tabular model was to use a logical OR operator “||” like below:
One of the coolest features in Power Pivot is the ability to define KPIs based on calculated measures. You can create KPIs in SSAS Tabular as well. Unfortunately, this feature is missing from Power BI. In this post I show you a very simple way to import KPIs and use them in Table, Matrix, Multi-row card and Card visualisations in Power BI.
I use the word “IMPORT” as this feature is NOT available in Power BI Desktop yet so we CANNOT create KPIs directly in Power BI Desktop, but, there is work around for it that I explain it in this post.
Latest version of Power BI Desktop
Microsoft Excel (2007 or later)
Power Pivot add-on if using Excel 2007 to 2013 (Power Pivot is already available in Excel 2016)
create desired KPIs on top of your calculated measure(s)
save the Model (Excel file)
import the Model to Power BI Desktop
Let’s go through the whole process step-by-step to see how it works on real world.
Note: I use Excel 2016 and Adventure Works DW SQL Server sample database. If you’re using prior versions of Excel, you have to download and install Power Pivot for Excel. All steps below are pretty much the same.
Open Excel 2016
From Data tab click “Manage Data Model”
Note: In case you’re using prior versions of Excel you need to click “Manage” from Power Pivot tab. All other steps would be the same.
Get external data from SQL Server
Enter server name and database name then click Next
Select “FactResellerSales”, “DimProduct”, “DimProductCategory” and “DimProductSubCategory” then click Finish
SSAS 2012 supports three different approaches for creating a BISM (Business Intelligence Semantic Model):
a. Uses relational modelling constructs like such as tables and relationships
b. Uses xVelocity in-memory analytics engine for sorting and data calculations
c. Needs to use SSDT (SQL Server Data Tools) to implement
d. Can import data from relational data sources using OLE DB native and managed providers
e. Tabular solutions only support one model.bim file per solution, which means that all work must be done in a single file. Development teams that are accustomed to working with multiple projects in a single solution might need to revise how they work when building a shared tabular solution.
f. support DAX calculations, DAX queries, and MDX queries
g. Tabular model databases can use row-level security, using role-based permissions in Analysis Services (DAX implementation required)
h. It might be not a good choice for the systems that are going to load terabytes of data