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).…

  • K-Means

    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. Re-shape layout of tables 6. Sub-sets 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 sub-modulefrom 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…