Practice files: https://drive.google.com/drive/folders/1vmNlWsP55IHT4wrB7enVZApSNgVYUuqz

Basic Shortcuts and Tips

• Shift F11 to add new sheet on right side, R Click add to add to left of it
• Ctrl W to close book but keep excel open
• When using fill arrow, small box at end has option to just fill the formatting instead of series
• Select whole column to autofit width, if only content cells selected, then go to home tab > Format option > autofit column width. Can use when you don’t want col to expand to size of largest value but only to the one selected by you (Alt H O I)
• Ctrl D to fill down, Ctrl R to fill right. Eg, Select a cell range, type formula in 1 cell after or before selection and press the shortcut key
• Ctrl + (from numeric pad) to insert column, or Ctrl Shift +; Ctrl Alt + to zoom in & – to zoom out
• F9 to see calculation result of selected formula while editing formula. Ctrl & Drag cell (from any border) to copy it to other location
• Getting Day from Date using TEXT function to format anything =TEXT(D3, “DDD”)
• Drag from selected cells border to cut paste. Shift drag from border to insert in between columns and shift rest cells forward
• ALT + Enter to add new line in same cell
• IF formula with multiple conditions but same value using OR or AND function insider. Eg: IF(OR(A2<50,B2<50),”FAIL”, “PASS”)
• Nested IF formula with multiple values. Put the IF function value for true, instead of value for false, write another IF function

Data Validation and Conditional Formatting

• If data validation already exists and you select ones which don’t have it as well, it’ll prompt you to extend it or just apply to already existing dropdowns
• Conditional fomat (CF) whole column based on 1 cell. Eg: In attendance sheet color whole column read if heading is “Sunday”. So select whole range, new rule in conditional formatting, =D$2=’Sunday’ and apply as it keeps row fixed in horizontal sheet
• CF has ‘Date Occuring’ option having yesterday, next week, next month, etc
• CF for top 10, above or below avg
• CF if applied icon or anything, don’t want to display value of cell then open Format dialog box > custom > put only semicolon
• ROW() gives row number, MOD(number, divisor) gives remainder. Useful to CF alternate rows. Eg after every 2 rows highlight so if MOD ans = 0
• Dynamic CF: Add radio button from developer tab. R click > Format controls > Control > Cell Link give a cell reference. Duplicate ratio btn & it’ll increment cell value based on option. For CF rule, use AND formula a cell containing a specific word (option of radio) & linked cell containing number that corresponds to radio btn selected (Video 148)
• In formatting color, you can also custom format cell and display a text for a condition like marks between 1-20 show “less”

DATA Tab (Filter, Consolidate, What If Analysis)

• Advanced Filter option to copy the filtered data at another location and where the criteria is also mentioned at other location with heading and value. Criteria can have signs like ‘>20’, ‘(asterisk mark)A’ means having A character anywhere in it (like normal regex). If only want specific heading cols filtered, have range of copy to location
• To copy to another sheet, be on that sheet when applying, select range from the data sheet. Can be made dynamic (auto update) using other way
• Consolidate option in Data tab is useful for rollup of data in main sheet. Eg: Monthly expense sheet and want to get sum of all values in a year sheet, so select year sheet cell range, consolidate, give reference to monthly sheet cell range (make sure all other sheets have values in same range), select action like sum and apply, tick “create links to source” to autoupdate if changes made. References are saved so reselect the cell range, click consolidate and apply to update values
• 3D reference Formula to consolidate values =SUM(Jan:Dec!B2) will get sum of B2 cells from sheet jan to dec, if space in sheet name then put inside single inverted commas the sheet range before ! mark. Can also perform other functions like Max, Min, Count
• Data Validation: To avoid entering duplicate values, select the column like D, apply validation > custom > formula as =Countif(D:D,D1)=1 means in range D:D the cell D1 should appear max 1 time
• Can show input message like comment when cell selected
• If you want to create dependent dropdown, then data validation from list but if you have given the range a name so instead of referencing range use the forumla =Indirect(E2) where E2 is cell containing list name like Cricket, and it references list of players
• What if analysis: Scenario manager is to have different values at same place and view them as per scenario or create and can also have summary table created for it
• Goal Seek: Have a cell with formula and use goal seek, select that cell, set what the value should be, and tell which cell it can change to get the value you mentioned
• Data Table is used to Goal Seek for multiple variations based on a formula: https://youtu.be/JJ80vMasdUo Like EMI table for variations of amt and time like 5L for 1-5 years, 10L for 1-5 years
• Sort a column, Select the array with it’s heading, Subtotal option, what column to group by, sum or other operation, on what column to perform operation, it’ll give group wise subtotal. Select all, click subtotal, remove all. Can also have page groups
• Group columns and when collapsed, they won’t show in print
• Solver addin enable from excel options, use like advanced goal seek

Home Tab

• Format painter double click to paste formatting of selected cell multiple times
• Open clipboard pane to have the history with formatting. Paste all option available as well
• Alignment group has indent option. Every group bottom right corner has more options
• Protect Sheet option in Format option in Cells group of home tab. If want to enable only few cells editing, then select the column or cell, go to format, click on Lock Cell to unlock them (know if it’s enabled by seeing if lock icon has grey box). Then when you protect sheet, it’ll only apply to locked cells. More options in protect sheet like “Select lock cells” uncheck so they can’t even select that cell to see formula but only unlocked ones which is useful to not allow seeing formula
• Find, Replace by match case, cell content, formatting.
• Go To Special: Constants & Formulas (Logicals means True or False), Object selection (use tab to switch between them), Row Difference (selects cells in every row which has different value than all other cells in the same row) & Column Difference, Precedents (cells referred in the formula or which are being used) & Dependents (highlights which cells are using or depending on the currently selected cell for value), Last Cell (Goes to the furthest cell in whole sheet), Conditional Formatting & Data Validation
• Paste Special: Normal,
  • With Formula: Formula only (no formatting), Formulas and Number formatting (comma, decimals), No borders, Keep source column width (keeps size of column which isn’t in normal paste), Transpose (Rows and columns interchange)
  • With Values: Paste values (no formula), values & number formatting, values & source formatting
  • Formatting only (no data only design), Paste Link (uses formula to reference respective cells of copied data so it updates), Picture (image transparent & can do formatting like border), Linked Picture (updates data in the image)
  • More Options: Skip Blanks (Copying range havings blanks in between, on paste over other data, blanks with not be pasted & old data will remain as it is, useful for merging 2 columns if each has only some cells filled)
  • Add, Subtract, Multiply, Divide (Copy 1 cell, then select a range, paste special any operation then it’ll perform it) Eg: Cell with “10” copied & pasted over range as division then it’ll divide each cell in the paste area & divide by 10

Insert Tab

Can insert chart, tables, watermark by adding it in header

Pivot tables

Each heading different, No merged headers, no full blank rows (few cells ok), paste it below 2-3 blank rows so filters can appear there

• Analyze tab > Options > Show Report Filter Pages (Creates new sheet for each value in the Filter field of pivot table) (Useful); Refresh all option; Change data source option use if data range expanded
• Right click for pivot table options, show different text for empty or error cells, autofit; Show row & column grand totals, display (filter icon and small things), printing (repeat row label on each page, print title, expand collapse btn), Data tab (refresh when opening file)
• Pivot table apply filter > value filter > Top 10 then can change number to top 5
• Auto refresh pivot table https://youtu.be/azmZGcSvmrA
• Refresh All twice to see changes in pivot table

Pivot table, pivot chart, slicer, timeline, grouping

Grouping (right click group, like by days and number as 6 so gives 6 day group ranges, can select range and do custom groups too)

• To view top 10 names, right click row label and apply value filter
• For text boxes, format shape and make margins 0
• Design > Report Layout change to tabular form to show heading of labels
• Design > Move chart to have only chart on another sheet. Add Chart Element > Legend > move it to top. Switch Rows & Columns. Change Chart type
• Chart size changes when cell size changes, so go to chart, r click, format chart area, size and properties settings
• Right click Bold value & untick Subtotal to hide it
• Slicers are quick filters (Analyze > Insert Slicer). Ctrl Click to multi select. In options tab > adjust columns. Multiselect option in slicer. Inner dimensions. Options > Report Connections to connect one slicer to multiple pivot tables, or just copy existing table & it’ll auto copy it’s connection. Slicer Settings > Change name, sort order, hide items with no data. Slicer style to change hover and selected cell color
• Time Line. Drag Date field in “Filters”. Select it, Analyze tab > Insert Timeline
• In cell, add = sign then click in pivot data, it’ll add formula of GetPivotData, edit it to get data you want
• Pareto chart used when 2 charts need to be combined but axis vary. Eg: Sales of A person and Overall Sales of company. R click > Format data series of Overall sales bar in chart to secondary axis (there will be 1 axis on left, 1 on right). Then right click that color and change chart type to line or other
• Right Click chart > Select data option > Can edit axis lables and give a range from another table. Select data option is useful and helps add another data to existing charts & then can change it’s type. Eg: Bar chart and line chart of 2 different tables
• Format axis and fix bounds maximum minimum so chart doesn’t change axis values based on chart bar height
• Chart types: Infographic (progress wheel in %), Trend, Map, Male Female comparison chart (https://youtu.be/qCjJvbbh8qA)
• Chart hyperlink text box to show that field in chart https://youtu.be/x-tzqZLviCU https://youtu.be/JTd1HiF9r7c

Power Query

Merge multiple sheets using Appended query and advanced editor
https://youtu.be/uBP1cNkHffY Can also create pivot table from it

Click any step in between to insert steps after it. Close and keep changes if editing a connection and don’t want table to be loaded on a sheet

• Save and Load To option let’s you have table on new sheet or only create connection so can also be used in appending without editing code. From pane, right click connection to use “Load to” option, double click to edit in powerquery
• Choose Column to keep selected ones, sord heading order, go to column. Remove other column remove non selected ones
• Click cross in steps to remove it. Double click step to edit settings
• Keep top rows, bottom rows, range (start and how many after it), keep duplicates option removes unique values appearing only once, keep error
• Remove top rows, bottom rows, alternate rows (1st row to remove, no. to remove like 1, no. to keep like 1), duplicate, blank, errors
• Add Columns > Conditional Columns: Specify condition like X column values equat to are start with, if true then what value in new column. Eg: Zip code starts with 10, put local or not_local
• Split columns By multiple characters (normal way allows only 1 character), left most right most or all occurences, by no. of characters, position, upper lower case, digit non-digit. Into rows or cols (duplicates other cols when duplicated)
• Group By Perform operations on specific group like subtotal. Select group by column, new col name, operation (sum, count, min, max, median, count distinct rows, etc), col to perform it on. Advanced Group By for multiple column grouping (creates rows for each combination). Eg: Group by state of sales and dealer or end user status, with total revenue, will give 2 rows of each state, 1 having dealer revenue and 2nd having end user revenue
• Data type for field, first row as headers or not, replace values or special characters like tab, line feed (enter)
• Transform Data Vertically Eg: Product and category columns, then zone 1, zone 2, 3, 4 cols. Now want to keep product and cateogory and want pivot like data of zones. Select product & category col, R click, unpivot other cols https://youtu.be/kyuDDo5JEhU
• Have more rows instead of more columns for addition of new data like in Pivot table. So select columns and use Unpivot Columns option in Transform tab
• Combine 100+ tables in power query. Create connections and append table. Old Method without creating connection: New query from other source, blank query, enable formula bar from view tab, =Excel.CurrentWorkbook() press enter, to get table list. On content col, click expand button, uncheck use col name as prefix, ok. Click on source step, filter name to not include Query, just have Tables. Click on expanded col step, close and load to. https://youtu.be/JWARWj3Puww
• Alternate of Vlookup: Create connection of 2 tables or import in another workbook. Merge as new option, select common column in both tables, join kind as left outer, click expand btn for new col and select which cols of table you want. Fuzzy match for ignoring case, match by combining text parts. Ctrl click to select multiple columns for creating connection & they’ll have numbers
• Add Column > Standard, can perform operations on selected cols
• Merge as new option of Left Anti will keep all rows in 1st and remove ones appearing in 2nd. Can use to remove holiday dates, add new day column, filter remove weekends, in final data it’ll be removed
• Add Column > Date options: Make sure column field type is date and not date time if time is 0. Calculate Age (Then add duration column for total years). Difference between dates Select 2 cols, Time > Subtract to find difference, select and click Duration of days or hours. In Date, can get start or end date of year (then get day of it), month (days in, end, start, name), quarter of year
• Fill up option fills null cells with values below it
• Merge workbooks without increasing size and use Pivot Table: Get Data > From File > From Folder (can also import files from nested folders). Select folder and click Edit. Delete other cols, Expand content. Save and Loat to Create Connection and tick “Add this data to the Data Model” instead of loading big data. Insert > Pivot Table > Use an external data source > Choose Connection (Select that query)
• To also have file name column, import in power query, delete all except content and name, split and delete .xlsx from name, expand content, remove steps until invoke custom function step remains, expand Table cell content, then each entry will have col with file name
• If you only want numbers in a col, then change data type to whole nos. and Remove Rows > Remove Errors
• Custom Calendar creation: https://youtu.be/tRT6x3b_UfM

M Query

M Query is language used in power query steps, this code can be shared to add steps at once in Advanced Editor option
The other person will click on “New Source” > “Blank Query”, then click Advanced Editor and paste the code, apply the steps

Click on “Add Column” tab > “Custom Column” to use M Language
Start formula with function name and similar type as Excel formulas

Date: Date.IsIn…
Text

Add blank query and in formula bar write: =#shared (Enter. Click on convert to table) Same can be done in normal Excel blank query & it’ll show list
It’ll give list of 900+ M functions. Use filter for name to know specific type functions. Click on blank space of that row and it’ll show how to use it and its details along with example and output

Other Small Tabs

Page Layout: Margins, print area, print title

Formula tab: Press F3 to open name manager while writing formula
Show dependents arrow, show formula option to display all cells formula, Error Trace, Evaluate formula option to check steps
F9 to check result of selected formula or individual cell content (calculate now option in formula tab)

View tab: Custom Views option to define view name and jump to it anytime, zoom to selection, New window for same sheet

Formulas

• VLookup: Exact match, approximate match (nearest value in ascending order of lookup). Use MATCH formula in it to get col no. Use with Concatenate formula if want to lookup a combination of cols, create concatenate values col & hide it and use it as lookup value. To lookup 2 values, create support column & concatenate columns having those 2 lookup values to create unique keys. Can do without support col using Choose formula to create virtual table with combined key, & it’s array formula so ctrl+shift+enter (video 127)
• HLookup: Use row no.
• Double VLookup: If want to lookup by any of 2 cols, use iferror vlookup and change array change to have lookup value in left most. Eg: Part No./ Serial No. (enter any)
• Nested VLookup, IFS (better but no False value)
• Lookup: Select column to lookup value in (forward or behind) instead of putting col position
• Small formula with position attribute to return that much smallest no. and removes blank spaces in result
• Countif: Select array, criteria that can be word or comparison operator in “” like “>=5000” or select other cell with the criteria. Video 117 for auto sort. Video 118 to get rank using countifs
• Sumif, Sumifs, Averageif: For wildcard criteria use star "*"&B2&"*" which means it’ll search B2 in criteria range as containing rather than exact match. If want to check on fifth position then “?” represents each character "????"&B2&"*" Can also have conditions instead of star like “>=”&B2
• Index Match: Index formula accepts array, row, col & returns value. Match accepts lookup value and single row or single column selection and returns the position. Video 110 helpful to get value from table based on 2 dropdowns. For Custom Dynamic list, using Index Match formula. Index returns an array when dragged down
• SumProduct: Sums the product of 2 arrays. If you want sum of alternate rows, add helper column with 1 then 0 and repeated, then sumproduct will give sum of values having 1 in front or use MOD formula to divide ROW() (have array selection in row formula) by 2 and get remainder as 1 or 0 & Ctrl+Shift+Enter. +1 after ROW()+1 to do opposite. Or use Sum & IF formula with MOD & ROW = 0 and return array
• Left, Right, Mid and FIND: Find character in cell starting from position like 1, Left gives the specified no. of characters from left. Use LEN – FIND to get position from right. MID takes cell, start num, num of chars
• REPT, Substitute: Rept repeats a string specified no. of time
• PMT (EMI), PV, FV, IPMT, PPMT: PMT divide interest rate by 12, PV is loan amount & add -ve sign to it in formula. IPMT gives interest amount, prover “per” as month number in formula. PPMT gives princpal amount of emi
• Upper, Lower, Proper, Trim
• Datedif, Date to Text, Text to Date, EOMONTH, Workday.Intl: Datedif returns date difference, accepts target date of past, current date, format in “”, eg: =Datedif(“20/8/1999″,today(),”m”) will give difference in months. y m d, “ym” gives months above completed years, “md” gives extra days over months. =Text(A2,”dd/mm/yy”). Convert text format to date using =Date(Year(B2),Month(B2),Day(B2)). To change mm/dd/yyyy format to dd/mm/yyyy, 1st extract y m d from mm/ , combine using =date formula. EOMONTH returns end date of month & do +1. Workday.Intl(start date, days, weekends, holidays) provide no. of working days, which weekends, list of holiday dates

Database Formulas (has headers in a table format)

• DGET: database fix range, field (column heading name to pick from or col no.), criteria (select heading & value in a range like lookup) & can have 2 or more criterias, just need to be adjacent. Can also do lookup to left
• DSUM, DAVERAGE, DCOUNT (only counts number cells), DCOUNTA (counts no. & text cells), DPRODUCT, DMAX, DMIN
• Offset: Returns referenced cell. Give reference to start from, no. of rows & cols to move away to, height & width gives an array range starting from where you move to. Can wrap offset formula in sum formula as well. Eg. Dynamic table having new entries added, you can get sum of last 6 rows using offset & counta formula in it
• MAX IF / MIN IF: MS 2019 has inbuilt formula of MAXIFS & MINIFS. Max formula checks If condition that a column range has a specific value, & if true then returns the numeric column range. Make it array using Ctrl+Shift+Enter.
• Choose: Assigns index number to particula value or formula & returns that value when index number mentioned. Eg- =Choose({1,2,3},43,52,62) Instead of curly bracket, can have match formula to return an index, instead of value, can have values corresponding to index no. Video 121, 122. Can swap columns (dynamically rearrange) in excel like giving priority to which column appears where (useful). Choose is for picking value at a specific index value within the formula. Can do reverse vlookup by creating virtual table using choose
• FILTER: Give full array, include value as column range = “value”
• SORT: Give array, sort col index, order +1 or -1
• Aggregate: Auto adjust sr no in Col A if filled in Col B. It accepts function like count represented by number like 2, then what it ignores, then array it performs on. Eg: =IF(B2=””,””,AGGREGATE(2,5,$A$1:A1)+1)
• MODE: Mode gives most repeated value. Use index mode match to get value https://youtu.be/_tRuDyI_a0w =Index(C2:K2,Mode(Match(C2:K2,C2:K2,0)))
• RANK.EQ: Give number, array, order ascending or descending & it’ll give ranking
• XLOOKUP: Lookup value, lookup array (select range in which to search), return array (range in which result is there). For return array, use index match to get array of selected column & have row as 0, or select column, or select multiple columns & it’ll give that range. Without index match, use xlookup same & for lookup array xlookup heading name like match. If not found argument is like iferror & can use another xlookup or return text. Match mode is exact match, next smaller if no exact found, next greater (no need of ascending order which is in vlookup, can be random in xlookup), Wildcard match is done using star. Eg: Return full name when 1st or last name entered =xlookup("*"&E2&"*") (having star in quotes in lookup value, if star not at start then what you lookup is 1st word, video 139), Search Order is first to last, last to first, ascending, descending
• XMATCH: Same as Match (returns position) but can have match mode (next smaller, next larger, wildcard) and search mode. If wildcard mode, the lookup value can have star symbol in the lookup cell itself or in formula
• UNIQUE: Select array, by rows is default & can have by cols, exactly once as true hides values that appear more than once completely, false gives atleast 1 entry for duplicate values
• RANDARRAY & SEQUENCE: Random array accepts rows cols min max integer or decimal. Sequence accepts rows cols start (starting no.) step (gap between nos)

Tips and Tricks

• If want to create multiple sheets with names, select name values range with heading, create pivot, add it to “Filters”, go to Analyze tab > Options > Show Report Filter Pages (Creates new sheet for each value)
• For editing multiple sheets at once, select all using shift click, set zoom in level for all, perform action. Right click on sheet bar arrows to jump to other sheets
• Excel to powerpoint. Create chart and paste special with link data option, as picture. Powerpoint > Insert > Table > Excel Spreadsheet then you get all excel options. Double click to get excel options, click out to hide
• Change icon by creating shortcut > properties > change icon. Can add msg box on file open using vba
• To use number to text “spellnumber” forumla in all books, add vba code in file and save as excel addin file. Go to excel options > Addin, add that file. Also go to trust center > macro settings > tick trust access and enable all macros. Then that formula will work in all books when added using addin file
• Custom cell format: Press Ctrl + 1 > custom > [=1]# "Liter";# "Liters" bracket is for value condition, # is for number input then space & text. Instead of # can put 0.0,, (0.0 for decimal & “,” for thousand separators). Hide value by puttin only “;”. For color text 1st bracket is for +ve nos., -ve, 0, text. [Blue];[Red],[Green];[Black]
• Select column headings, search Form, can do data entry. If already data in table then select whole table with headings then click form. If in quick access tool bar, press Alt & position number of tool
• Excel Settings: Custom Lists: File > Options > Advanced > Edit custom lists (like if Jan in cell and dragged down, it’ll fill next months name). Proofing > Add Autocorrect option for acronym words you type frequently. Advanced > Enable fill handle and cell drag and drop
• To convert number in text format use =Value formula. ABS to convert -ve no. to +ve. AND, OR, EVEN (returns next even no.), ODD, FACT (Factorial), ROUND, GCD, ROW, COLUMN
• Camera option creates an image of cell which updates automatically as cell does, can resize it & drag
• Auto-filter Click on cell & click auto filter, it’ll apply filter to the heading with value equal to cell you selected or if cell has any condition. Eg- cell has “>29” then will filter heading values to show only nos. above 29
• Searchable dropdown list (Video 145 https://youtu.be/uOTAoOkppyM) using formula of FILTER, ISNUMBER, SEARCH, data validation followed by # to display array and turn off error alert
• Insert Maps chart, expand to show numbers and color grading, format data labels to show state names, etc. Only for states, not city

Data Modeling

Create relationship between tables. Primary key is column in which all values are unique. Foreign key is in which values repeat multiple times.

Snowflake schema is when there 2 or more degree connections between tables. Can have multiple direction connections if you want a table to act as bridge between 2 tables for key

DAX

Syntax:
Measure Name = function(tableName[columnName]) Total Sales = SUM(Sales[SalesAmount])

Add all DAX functions under one table, create new and name it as “All DAX” so it’s at top and easy to track
To move measure to another table, select it, under Measure Tools tab change the Home table dropdown

Calculated columns (Add column in Table Tools)
In excel you clicked on cell to mention it, here you mention table name and column like in above formula, if want to pick from same table, start with square bracket

Explicit measures are reusable and recommended (created by you) and implicit are not (like default functions for value fields such as sum average count that is in pivot table also)

Add comments using 2 forward slash //
Similar to excel formula but can create charts with multiple filters alonside this custom “meaure”

Useful functions
Sum, Calculate, Divide (has alternate value if division returns 0), TotalYTD
Date: EOMONTH, FORMAT, WEEKDAY, EDATE, DATEDIFF, DATEADD (For time internal in time intelligence function), SAMEPERIODLASTYEAR (Just mention date and use in filter arg of Calculate function)
RANX function to give rank based on attribute

SUMX to sum the product of 2 columns for each row in the measure’s formula
`Total_Rev = SUMX(Sales,Sales[OrderQty]*Sales[Price])
Or pick price from another table using RELATED function

Use 2 ampersand && for AND condition, 2 pipes || for OR condition in logical statement for IF formula.
`TargetCustomer = IF(Cust[Gender]=”M” && Cust[AnInc]>150000, “Yes”, “No”)

VLookup alternative (must have a key connected between table) using RELATED function
`Price = RELATED(Products[ProdPrice])

Statistical: DISTINCTCOUNT (Counts no. of unique values in a column)

“Calculate” functions helps compare same data set with different time period (time intelligence function) or a filter. First arg is expression or column on which you want to calculate then the filter condition
`Weekend Offer = CALCULATE([Total Order], ‘Calendar'[Week Status]=”Weekend”)

By default the grand total is based on filter you applied in report, if you want grand total or any value from whole table, use ALL function. Eg: when want how much percentage orders are of a category from the whole sales
All Order = CALCULATE([Total Order]),ALL(Sales))

`Previous M Rev = CALCULATE([Total_Rev],DATEADD([Date],-1,MONTH))
-1 is previous 1 interval, and interval scale is month

In Table Tools > New Table is used to create table based on DAX function or Calculate
Quick Measure options uses AI to auto recommend adding measures

Visualization

Click on any chart’s specific part and all other charts on the dashboard will be filtered by same

Hierarchies
Eg: Categories > Sub categories > Products
You have 3 different columns, drag other 2 and drop over “Categories” column in Report View, Fields tab, it’ll create hierary, drag that into the “Axis” tab of Visualization. Now the chart will have option to drill down i.e., click on a category bar chart, it’ll show sub categories. Turn off drilldown button to see highlighted filter as normal

Dual Axis Chart: Line and Stacked column chart used when 2 axis needed but units difference is huge. Eg: No. of units sold and Revenue have huge gap in numeric value

In Analytics pane under Visualizations tab, can add trend line, constant line (that shows dotted line for your targets or any value), min max median line with different colours, forecast

Decomposition Tree chart shows breakdown of the factors you choose in tree format. There’s AI (lamp icon for all such), that shows highest influential factor or lowest contributing. Eg Sales breakdown shows US country as highest value which further shows Urban category

Key influencer visual Shows what influences and by how much value

Slicers Filters whole dashboard

Map chart Make sure to select column and set data category if it’s country, city, etc. Filled option also available

Format painter allows to color scales conditionally. Tooltip values.

Double left click in blank area and use AI to ask queries, can turn answer into visual

Bookmarks Filter the view by clicking on specific chart sections, and save that view in bookmark

Add text box, and in “Action” property, can define what it does like navigate back, go to page, go to a bookmark view

View tab > Selection option to show or hide visualization charts and update your bookmarks

In Modeling > Manager Roles, create create roles having filtered data using DAX like only a country’s data visible to a user role level