Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models

Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models
Photo by Markus Spiske

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 Power Query

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
                        )
Time Dimension in Seconds Level with DAX
Continue reading “Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models”

Quick Tips: Boolean Conditions when Querying SSAS DMVs

Boolean Comparison in SSAS DMVs, Error: A Boolean expression is not allowed in the context

If you are querying SSAS DMVs you may want to add some conditions in the query.

Something like getting all active relationships, perhaps like below:

select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS where IsActive = 'true'

Running the above query on an instance of SSAS Tabular gives you the following error message:

Error: A Boolean expression is not allowed in the context …

Fixing this is quite easy, run the below query to get active relationships:

select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS where IsActive

Boolean Comparison in SSAS DMVs

And to get inactive relationships run this one:

select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS where not IsActive

Boolean Comparison in SSAS DMVs

Quick Tips: Keyboard Shortcuts/Hotkeys When Writing DAX in Power BI Desktop

DAX Keyboard Shortcuts in Power BI Desktop

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

Continue reading “Quick Tips: Keyboard Shortcuts/Hotkeys When Writing DAX in Power BI Desktop”