Category: Uncategorized

DAX – Creating Multiple Measure With DAX Query Editor
DAX – Creating Multiple Measure With DAX Query Editor
Creating multiple measures in DAX Query Editor It may be useful to create templates for creating DAX models, using commonly used DAX measures. Here we create a template for commonly used sales measures, such as Sales YTD, Sales Last Year, Sales Year on Year, and Sales Year on Year %, we can then apply the…

DAX – Using the Index Function
DAX – Using the Index Function
Demonstrating the use of the Index function with the Contoso Retail Data warehouse. We can start off by building a virtual table in the DAX query editor, which we can use to apply the Index function. The table creates is a list of the first 10 customers by customer key from the DimCustomer table. If…

Building a Measure with the DAX RankX Function
Building a Measure with the DAX RankX Function
Using the RANK() Function in DAX To demonstrate the use of the DAX function, we’ll start by creating a simple sales table with the sales amount and the year. Then we’ll add additional columns to create examples of using the RANK function. In the first example (SimpleSalesRank), we’ll just create a simple ranking. The default…

SQL Sales Report
SQL Sales Report
We can create a more interesting sales report using Window functions and additional calculations in the query, including the gross margin %, total profit, and ranks for product price, sales quantity, and sales rank. The query uses a Common Table Expression (CTE) function to group the data first (as we can’t group using Windows functions).…

KMeans
KMeans
Supermarket Example Import libraries and read in CSV file to data frameThe data comes from kaggle here (mall data) First, we run some basic checks on the data to check for data integrity.Data includes 200 rows of data by 5 columns We can change Gender to numeric with the following Then we check for nulls…

Pandas Notes
Pandas Notes
Notes from Getting Started Tutorial:https://pandas.pydata.org/docs/getting_started/intro_tutorials/03_subset_data.html 1. Creating a Dataframe and Series 2. Create Plots with Matplotlib 3. Add new columns from existing columns 4. Summary Statistics 5. Reshape layout of tables 6. Subsets and Filtering import pandas as pd#read csv file to pandas DatafFrametitanic = pd.read_csv(“titanic.csv”)air_quality = pd.read_csv(“air_quality_no2.csv”, index_col=0, parse_dates=True) #save as excel, index=False removes…

DAX Code Examples
DAX Code Examples
DAX Code Examples:1. Using Variables2. FORMAT()3. HASONEVALUE()4. AND, &&5. CALCULATETABLE() and SUMMARIZE()6. USERELATIONSHIP()7. SWITCH()8. ISFILTERED() and making visual transparent9. SELECTEDVALUE() and creating a dynamic Graph Title10. FILTER and ADDCOLUMNS11. RANK() VAR: Using Variables Running Total = VAR MaxDateInFilterContext = MAX ( Dates[Date] ) //variable 1 max date#VAR MaxYear = YEAR ( MaxDateInFilterContext ) //variable 2…

Lists in Python
Lists in Python
Create a listSimple listmylist = [“apple”, “orange”, “banana”, “mango”, “pineapple”]mylist[2] # returns ‘banana’mylist[1] # returns ‘pineapple’mylist[2:4] #returns ‘banana’, ‘mango’, ‘pineappleif “apple” in thislist: print(“Yes, ‘apple’ is in the fruits list”)mylist.insert(2, “watermelon”) #insert at position speifiedmylist.append(‘grapes’) #adds to end of listmylist = [“apple”, “banana”, “cherry”]tropical = [“mango”, “pineapple”, “papaya”]mylist.extend(tropical) # adds tropical list to thislistmy.remove(“banana”) #removes first occurrencemylist.pop(1) #removes specified instancedel mylist[0] #also removes instancedel mylist…

Linear Regression
Linear Regression
Linear Regression using Salary and Years of Experience Data Data Source: Salary_dataset.csv KaggleThe salary data set includes 2 columns: Years Experience which will be our independent variable (X) and Salary (Y). Linear regression is a fundamental statistical method used to model the relationship between a dependent variable and one or more independent variables. The primary…

Pandas Code
Pandas Code
Pandas Quick Guide Modules import pandas as pd #pandas moduleimport scipy.stats as stats #stats submodulefrom bs4 import BeautifulSoup #scrape moduleimport requests #API moduleimport whois as whois #whois module Filesdf = pd.read_csv(“CookieData.csv”) #import CSV file to dataframe df #define index column and convert dates to date format air_quality = pd.read_csv(“air_quality_long.csv”, index_col=”date.utc”, parse_dates=True) air_quality.to_csv(“checkcombine.csv”) #save dataframe to…