Skip to content

Instantly share code, notes, and snippets.

@aabundez
aabundez / reset_vs_environment.bat
Created Jun 9, 2017
Resets Visual Studio sign in
View reset_vs_environment.bat
Devenv /ResetSettings
@aabundez
aabundez / ssas_tabular_column_listing.dax
Last active Apr 12, 2018
Retrieve SSAS Tabular column definitions
View ssas_tabular_column_listing.dax
//Run in DAXStudio (http://daxstudio.org/)
select [ExplicitName], [IsHidden], [Type], [SourceColumn], [Expression]
from $SYSTEM.TMSCHEMA_COLUMNS
where TableID = 13
View dax_date_table
Date =
ADDCOLUMNS (
CALENDAR (
DATE ( YEAR ( MIN ( 'Sales'[Date] ) ), 1, 1 ),
DATE ( YEAR ( MAX ( 'Sales'[Date] ) ), 12, 31 )
),
"Year", YEAR ( [Date] ),
"Quarter Number", INT ( FORMAT ( [Date], "q" ) ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ),
"Month Number", MONTH ( [Date] ),
@aabundez
aabundez / Microsoft SQL Server Management Studio 17.lnk
Last active Nov 13, 2018
Runas shortcut for Management Studio. Update the Target property.
View Microsoft SQL Server Management Studio 17.lnk
C:\Windows\System32\runas.exe /user:DOMAIN\username /netonly "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe"
View Calendar.pq
let
Source = fn_Calendar(#date(2018, 8, 1), Date.From(DateTime.FixedLocalNow()), null)
in
Source
View DIAD_DAX_Samples.txt
//Date Table
Date = CALENDAR (DATE(2012,1,1), DATE(2018,12,31))
Date = CALENDAR ("1/1/2012", 12/31/2018")
//Time Intelligence function
PY Sales = CALCULATE ( SUM ( Sales[Revenue] ), SAMEPERIODLASTYEAR( 'Date'[Date] ) )
PM Sales = CALCULATE ( SUM ( Sales[Revenue] ), PREVIOUSMONTH( 'Date'[Date] ) )
//Ratios
% Growth = DIVIDE(SUM(Sales[Revenue])-[PY Sales],[PY Sales])
@aabundez
aabundez / Prior_Month_Sales.dax
Last active Nov 22, 2019
Calculate prior month sales without a Date table
View Prior_Month_Sales.dax
Revenue PM =
var prior_month_start_date = EOMONTH( MIN ( Sales[Date] ), -2 ) + 1
var prior_month_end_date = EOMONTH( MIN ( Sales[Date] ), -1 ) + 1
RETURN
CALCULATE(
SUM ( Sales[Revenue] ),
FILTER (
ALLSELECTED ( Sales ),
Sales[Date] >= prior_month_start_date && Sales[Date] < prior_month_end_date
)
@aabundez
aabundez / quick_prior_month_sales.dax
Created Nov 24, 2019
Prior period DAX calculation using automatic time intelligence in Power BI
View quick_prior_month_sales.dax
Prior Month Sales = CALCULATE ( SUM( Sales[Revenue] ), PREVIOUSMONTH( Sales[Date].[Date] ) )
// Quick measure uses DATEADD function instead
Prior Month Sales = CALCULATE ( SUM( Sales[Revenue] ), DATEADD( Sales[Date].[Date], -1, MONTH ) )
View fn_Calendar.pq
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
// Determine number of days between start and end date
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
// Add a day back, as Duration math above will give you the days literally between rather than inclusive. Ex Dec 31 - Dec 1 = 30 days, but we need 31 days in that period
DayCountAdd = DayCount + 1,
// Create a Dates list object starting at the start date, for the duration of the day count, increment of one year
Source = List.Dates(StartDate,DayCountAdd,#duration(1,0,0,0)),
// Turn that date list into a table
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
@aabundez
aabundez / Get-AADUserInfo.ps1
Created Oct 28, 2020
Powershell to extract AAD user info based on a list of AAD user IDs.
View Get-AADUserInfo.ps1
<#
.DESCRIPTION
This script imports a list of UPN's and extracts more user information from Azure Active Directory via the AzureAD PS module.
.LINK
https://docs.microsoft.com/en-us/powershell/module/azuread/get-azureaduser?view=azureadps-2.0
#>
$upns = import-csv "C:\Directory\File.csv" # Replace filepath here