There are several scenarios to use Unicode characters in Power BI including but not limited to:
Creating simple KPI columns in Table or Matrix visuals
To show the status of a measure more visually like using starts
Using Unicode characters as icons in your reports representing the subject
Chris Webb explained some of the above scenarios here.
In this post I explain how you can use Power BI as a tool to generate almost all valid Unicode characters in Power BI. You can download the PBIT at the bottom of this post. Then you can copy the Unicode characters from Power BI and use them in all textual parts of your report like visual titles, text boxes and so on.
The Unicode planes start from 0 to 1,114,111 which is decimal equivalent of 0 to 10FFFF in hexadecimal numeral system. For more information on Unicode planes check this out.
So, a simple way to generate all possible Unicode characters is to generate a list of decimal numbers starting from 0 ending at 1,114,111. This way we generate a series of decimal numbers regardless of the gaps between starting and ending Unicode blocks. Then using UNICHAR() function in DAX to generate corresponding Unicode characters. With the following DAX expression you can easily generate a list and the corresponding Unicode characters: Continue reading “Using Unicode Characters in Power BI”→
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
From now on I will post some quick tips to help you accelerate your Power BI Desktop development. As the first post of these series, I explain a simple way for hiding multiple columns in Power BI Desktop. To do so:
Navigate to Relationships veiew in Power BI Desktop
Maximise the table you want to hide some columns in
Select the first column
Pres Shift and use arrow keys to highlight multiple columns
Here is the trick: DO NOT RIGHT CLICK! Instead, press the menu button on your keyboard. If your keyboard doesn’t have contect menu button, don’t worry, you can press Shift + F10 to do the same job
On Saturday, 9th June 2018, we announced the existence of Power BI Documenter. As the name resembles, Power BI Documenter is a tool to help individuals and businesses to document their Power BI Desktop models. Everyone who already have several Power BI Desktop reports probably realized that documenting the solutions is not as easy as how creating a report in Power BI Desktop is. The issue is more visible in larger organisations with several Power BI Developers who are busy enough with a big list of tasks that are assigned to them on a day to day basis. Therefore, there is no time left to take care of the documentation. Every IT expert knows how important is to have proper documentation. We at Data Vizioner decided to do something tangible about this issue. So we started the project several months ago with the vision of creating web app to help individuals and businesses to keep their Power BI documentation on track. In this post I’m not going to explain how you can easily start documenting your Power BI Desktop reports using Power BI Documenter. You can learn more about Power BI Documenter and how to use it here. Despite the fact that the current version of Power BI Documenter is the very first version of the app with lots of limitations, it indeed can help users with their Power BI documentation tasks. All you need to do is to export the Power BI Desktop files (PBIX) to Power BI Template format (PBIT) and upload it to Power BI Documenter web app. Continue reading “What is Power BI Documenter”→
Date dimension has been discussed quite a lot on the Internet and you can find lots of valuable articles around it here and there. But what if you need to analyse your data in time level? A customer has a requirement to analyse their data in Minutes level. This means that the granularity of the fact table would be at minute level. So, if they store the data in their transactional database in seconds level, then we need to aggregate that data to minutes level. I don’t want to go there, just bear in mind that the granularity of your fact table is something that you must think about at the very first steps. In most cases, if not all cases, you’d be better to have a separate Time dimension. Then you need to have a TimeID or Time column in your fact table to be able to create a relationship between the Time dimension and the fact table. In this post I show you two ways to create Time dimension in Power BI:
Creating Time dimension with DAX
Creating Time dimension with Power Query (M)
Alternatively, you can take care of the Time dimension in the source system like SQL Server. Continue reading and you’ll find a T-SQL codes as complementary.
The techniques that I explain here can be done in SSAS Tabular model and Azure Analysis Services as well.
To follow the steps of building the test model you need to have:
Power BI Desktop: Download the latest version from here
A sample fact table containing time or datetime. I modified FactInternetSales from AdventureWorksDW and made it available for you to download in Excel format (find the download link at the bottom of the post)
DAX measures are the heart of every SSAS Tabular model, Power BI and Power Pivot solution. You write lots of DAX measures and you potentially reference some of them in other measures. So the number of DAX measures you write and reference them via other measures grow very quickly. Especially in complex solutions you may have hundreds of DAX measures. While your solution works perfectly, to make a minor change or adding a new measure to the solution or fixing a problem in your existing measures can be such a pain in the neck. In this post I’m going to take a step further and show you a simple way to get the whole data model dependencies then visualise the dependencies in Power BI. You can find the download link at the end of this post.
A simple search in Google brings you a bunch of useful articles talking about the subject. Some of the bests, in my mind, are as below:
In this post I use a DMV that gives us everything we want. ( Chris Webb already discussed the DMV here: Document Dependencies Between DAX Calculations). Running the DMV we can see what measures are references by other measures, what columns are referenced in the calculated columns and much more.
This is a very useful DMV that helps us getting a better understanding of the model we’re working on. We can also use this method for documentation.
How It Works
This method is fairly simple, you just need to run the following DMV on top of your SSAS Tabular model or your Power BI Desktop file and Import the results in Power BI.
SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY
For Power BI you’ll need to find the local port number then you’re good to go. The only part that might not look very straightforward at first, would be finding the database in Power BI Desktop model.
An easy way, after you find the local port number of an opened Power BI Desktop file, is to find the database name from SQL Server Management Studio (SSMS) when connecting to the Power BI Desktop model:
Select “Analysis Services” as “Server Type”
Type in “localhost:PORT_NUMBER” as “Server Name” then click “Connect”
Update 2019 April: If you’re interested in exporting the data model from Power BI Service to SQL Server check this out.
Note: The method explained in the above post involves Power BI Premium or Embedded capacities with XMLA endpoints connectivity at the time of writing.
A while ago I wrote a blog post explaining how to Export Power BI Data to SQL Server with R. In that post I explained how to get the job done in Power BI Desktop using R scripts. In this post I explain how to export Power BI Service data to SQL server. YES! You can export data from Power BI service to a SQL Server database sitting in your on-premises environment. Keep reading to see how.
How It Works?
This is going to be a short post as I already covered the first part of the process in my other post on Export Power BI Data to SQL Server with R. So in this post I show you how to use the Power BI Desktop file you already created using the method explained in that blog post to export your Power BI Service data to an on-premises instance of SQL Server. All you need to do is to
Publish the existing Power BI Desktop solution to Power BI Service
Install On-premises Data Gateway in PERSONAL MODE
Note: R is NOT supported by the current version (Version Number: 14.16.6614.5) of the On-premises Data Gateway in Enterprise Mode.
After you successfully published the model to Power BI Service you’ll notice that you cannot refresh the model if you don’t install the On-premises Data Gateway in Personal Mode.
About two years ago I wrote a blog post on how to connect to a Power BI Desktop model from different tools like SSMS, Excel and SQL Server Profiler. A big chunk of that blog post was about different ways of finding Power BI Desktop local port. Having the port number is crucial if you want to connect to a Power BI Desktop model from any sort of tools for different purposes like monitoring in SQL Server Profiler or analysing the model in Excel. I’m asked several times here and there about different scen people need to find Power BI Desktop local port number, so I decided to dedicate a post to Power BI Desktop local port number.
When you run Power BI Desktop, it runs a local instance of SSAS Tabular model in the background and it assigns a random port number to that local instance. Therefore, if you want to connect to the model to analyse it in Excel you need to know that port number. There are several ways to achieve that port number which this post will cover most of them.
As you may know, Power BI Desktop is now available as a Windows App that can be installed from Windows Store. In general, the main difference between the App and the downloaded edition from Micrsosoft website is that you will get notification whenever a newer version is available to download. Then you have to manually download and install the newer version on your machine, but, the App will be updated automatically. But there are some other differences that are out of scope of this article. There is just one difference which is relevant to this article which is that Power BI Desktop creates its temporary folders in a different location than the Power BI Desktop App does.
The methods described in this post works for both downloaded edition and the Store edition.
Different Methods to Find Power BI Desktop Local Port
· Using DAX Studio
· Tabular Editor
· Power BI Desktop Temporary Location
· Command Line (CMD)
The reason that I explain different methods is that not all methods are doable in all environments. You may work at a customer site that doesn’t allow you to install new software like DAX Studio on their machines. But you always have access on Power BI Desktop temporary files.