In an article I posted a while back I showed different methods of creating Time dimension in Power BI and Tabular models. The Time dimension I explained was in Minutes. In this post I show you simple way to create Time dimension supporting Seconds. As this is a quick tip, I only show you how to get the Time and ID columns in the Time dimension. If you need to add time bands (time buckets) check this out for more details.
Time Dimension in Seconds Grain with Power Query (M):
Copy/paste the code below in Advanced Editor to generate Time dimension in Power Query:
let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0,0,0,[ID]))),
#"Changed Type" = Table.TransformColumnTypes(#"Time Column Added",{{"ID", Int64.Type}, {"Time", type time}})
in
#"Changed Type"
Time Dimension in Seconds Grain with DAX:
Run the DAX expression below in a new calculated Table in Power BI or SSAS Tabular model:
Time in DAX = ADDCOLUMNS(
GENERATESERIES(1, 86400, 1)
, "Time", TIME(0, 0, 0) + [Value]/86400
)
In real world SSAS Tabular projects, you need to run many different testing scenarios to prove your customer that the data in Tabular model is correct. If you are running a Tabular Model on top of a proper data warehouse then your life would be a bit easier than when you build your semantic model on top of an operational database. However it would be still a fairly time-consuming process to run many test cases on Tabular Model, then run similar tests on the data warehouse and compare the results. So your test cases always have two sides, one side is your source database that can be a data warehouse and the other side is the Tabular Model. There are many ways to test the system, you can browse your Tabular Model in Excel, connecting to your Data Warehouse in Excel and create pivot tables then compare the data coming from Tabular Model and the data coming from the Data Warehouse. But, for how many measures and dimensions you can do the above test in Excel?
The other way is to run DAX queries on Tabular Model side. If your source database is a SQL Server database, then you need to run T-SQL queries on the database side then match the results of both sides to prove the data in Tabular Model is correct.
In this post I’d like to share with you a way to automate the DAX queries to be run on a Tabular model.
Straight away, this is going to be a long post, so you can make or take a cup of coffee while enjoying your reading.
While I will not cover the other side, the source or the data warehouse side, it is worth to automate that part too as you can save heaps of times. I’m sure a similar process can be developed in SQL Server side, but, I leave that part for now. What I’m going to explain in this post is just one of many possible ways to generate and run DAX queries and store the results in SQL Server. Perhaps it is not perfect, but, it is a good starting point. If you have a better idea it would be great to share it with us in the comments section below this post.
Requirements
SQL Server Analysis Services Tabular 2016 and later (Compatibility Level 1200 and higher)
An instance of SQL Server
SQL Server Management Studio (SSMS)
How does it work
What I’m going to explain is very simple. I want to generate and run DAX queries and capture the results. The first step is to get all measures and their relevant dimensions, then I slice all the measures by all relevant dimensions and get the results. At the end I capture and store the results in a SQL Server temp table. Let’s think about a simple scenario:
you have just one measure, [Internet Sales], from ‘Internet Sales’ table
The measure is related to just one dimension, “Date” dimension
The “Date” dimension has only four columns, Year, Month, Year-Month and Date
you want to slice [Internet Sales] by Year, Month, Year-Month and Date
It is easy isn’t it? But, wait. What if you have 10 measures related to 4 dimension and each dimension has 10 columns? That sounds laborious doesn’t it? Well, in real world scenarios you won’t slice all measures by all relevant dimensions, but, you still need to do a lot. What we are going to do is to generate and run the DAX queries and store the results in a table in SQL Server. How cool is that?
OK, this is how it works…
Creating a Linked Server for SSAS Tabular instance from SQL Server
Generating DAX queries using Tabular DMVs
Running the queries through Tabular model and getting/storing the results in a SQL Server temp table
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
