https://github.com/virajbhutada/bi-projects-collection/blob/main/U.S.
%20Healthcare%20Industry
%20Dynamics/report.pdf
One Directional Join Bi-Directional Join
The one side table can filter the other table but Both can change each other
the many side cannot
Try to avoid
1) When to use Bidrectional & one directional join?
2) Implicit v/s explicit Function :
Implicit is when u drag and drop the column in visualization and it automatically selects an
aggregate function like max, min, sum, count, etc
Explicit is when you separately create an aggregate function using dax formula
3) Fact table – Less Information but Pointers to other tables. Tall & narrow
Dimension Table – Contains info about dimension of data from a column in the fact table perspective.
Short and fat table, less rows many columns
Usually many-to-one relation, where fact table is in many side
Category of Functions
1) Columnar function - Usually aggregate functions (SUM, DISTINCTCOUNT, COUNT, etc) Columnar
function, does mot iterate through each row
2) Iterator Function - But if they iterate row by rowIterator Function (SUMX, MINX, etc)
3) Table Summarisation – Like Values, Summarize
4) Exclusion Functins – like Except, Intersect
5) Context Modifier - Time Intelligent functions, USERELATIONSHIP, ALL, ALLEXCEPT, DATEADD, etc
How to Identify Row Context vs. Filter Context?
To determine which context is applied, ask:
1. Are you inside an iterating function (SUMX, FILTER, ADD COLUMNS)?
o If YES → Row Context is applied.
o If NO → Check if CALCULATE or a filter is being used In CALCULATE()
2. Are you in a visual (table, card, matrix) that applies filtering?
o If YES → Filter Context is applied.
3. Are you using CALCULATE() to modify filters?
o If YES → Filter Context is being modified.
Remember Measure does not iterate row by row. Row Context functions
like SUMX, RANKX iterate row by row
DAX Queries
1) Switch
Here true() is used so that after each statement if the logic is valid, then it stops
moving to the next one. Just like Break() in Java
2) Expression Function (SUMX, MINX, MAXX, etc)
Specifically row wise but table should be mentioned inside the formula i.e. table’s row level
Measure v/s Calculated Column
Measured column is not added to the actual table whereas Calculated column becomes
another table. Measure just acts on the table by twisting data
Here’s a tabular comparison of Measures vs. Calculated Columns in DAX:
Feature Calculated Column Measure
Calculation Row-level (each row in a Aggregate-level (based on filter
Level table) context)
Stored in the data model Not stored, computed on demand
Storage
(increases file size) (more efficient)
Evaluation Computed when data is Computed dynamically when used in
Time refreshed a report. Changes according to filter
Can slow down model if too More efficient as it does not take up
Performance
many are created storage
Usage in Filters Can be used in slicers, filters, Cannot be used directly in slicers or
& Slicers and relationships relationships
Context Works in Row Context Works in Filter Context
Profit = Sales[Revenue] Total Sales =
Example - Sales[Cost] (computed SUM(Sales[Revenue]) (computed
per row) based on applied filters)
Golden Rule
If you need a value stored in the table and usable in slicers, use a
calculated column.
If you need a dynamic calculation that adjusts with report filters, use a
measure.
3) CALCULATE function is very important, can use filter within
DATE FUNCTIONS
4) TotalMtd, TotalQtd, TotalYtd- needed for cumulative figures
according to dates
Syntax same for all, if you need counting from specific date, explicitly mention as
above
TOTALMTD – cumulative total for that one month
Difference between TotalYTD and DatesYTD
In TotalYTD, we cannot use more than 1 filter inside argument . Two arguments
showed error while calculating cumulative value
In DatesYTD, also provides cumulative figures but we can use many filter but inside
CALCULATE(). Here two different filter calculated cumulative figure
5) YOY Comparison – SAMEPERIODLASTYEAR
6) PREVIOUSYEAR/MONTH/QUARTER
Use PREVIOUSYEAR when you need the entire previous year regardless of the
selection.
Use SAMEPERIODLASTYEAR when you need a like-for-like comparison (e.g.,
January 2024 vs. January 2023).
7) DATEADD- Compares values to the value provided but is cumulative in nature
Beside January 15, it will show the sales amount of January 1 (because -14 is there)
and according to DAY(mentioned inside argument)
8) DATESINPERIOD – To Calculate moving Averages
9) RELATED – To call columns from another table which is related the current table.
Returns Single value
added new column so
row by row it can iterate
needed row context
functions to iterate row by row
10) RELATEDTABLE –
👇 Summary Cheats
Use RELATED() when you want to bring one value from a lookup table.
Use RELATEDTABLE() when you want to aggregate or iterate over multiple related
rows.
11) ALL – Ignores all filters and returns the values
ALLEXCEPT – Ignores all filters except those mentioned in the argument and returns
the values
12) RANKX – Gives rank based on a certain column. But for multiple columns we need
cross join inside rank. Eg :
NB : If ALL is not applied, then all rows will provide rank 1 bcoz the context is the invidual
rows in the overall sales table and not the individual columns. ALL is for filtering in RANKX.
Substitute ALL with ALLSELECTED or ALLEXCEPT according to context
13) ALLSELECTED -
14) LOOKUP – Searches for a item in another table and returns corresponding value.
Sames VLOOKUP.
BUT
No duplicates allowed in powerBi, however if there are duplicate in excel VLOOKUP
fetches the first value
15) KEEPFILTER –
Here 3rd column ignores the filters of accessories, casual wear and only shows
values of Formal.
However with KeepFilter, it respects the filters and blanks out the other rows
16) REMOVEFILTER –
Difference between ALL vs REMOVEFILTER
ALL returns whole column, whereas REMOVEFILTER returns the column needed
But majorly same
17) SUMMARIZE – Returns a separate table which is disconnected, so filter/slicer cannot
be used. Use aggregator function to get summarized value
18) UNION,INTERSECT, DISTINCT normal concept
19) Var – Instead of hardcoding data, store it in variable
20) SELECTEDVALUE(columnName,alternateResult)
This function is particularly useful in measures and calculated columns to handle
scenarios where a single value is expected, ensuring that the measure or column
returns a meaningful value or a default value if the context does not meet the single-
value requirement.
Output visual
21) EARLIER
Basically used for cumulative figure. Refers to the previous cell in the specified
column
22) MAX, MAXX
MAX - Can only be used in a column but not on a measure
MAXX – Finds values in all table, and can perform calculations, filter, etc also
23) TOPN
It is used to return the top N rows from a table based on a specified column
Most Commonly Asked DAX Functions in Data Analyst Interviews
Here’s a categorized list of the most frequently asked DAX (Data Analysis
Expressions) functions in Power BI interviews:
Aggregation Functions (Basic but Essential)
Function Description
SUM Returns the sum of a column.
AVERAGE Returns the average (arithmetic mean) of a column.
MIN / MAX Returns the minimum or maximum value of a column.
COUNT / COUNTA Counts the number of rows (excluding or including
Function Description
blanks).
COUNTROWS Counts the number of rows in a table.
DISTINCTCOUNT Returns the number of unique values in a column.
🔹 Example:
Total Sales = SUM(Sales[Revenue])
Filter & Condition Functions (Dynamic Filtering)
Function Description
FILTER Returns a subset of a table based on conditions.
Ignores filters applied to a column or table. Entire
ALL
aggregation is returned
ALLSELECTED Ignores filters except those applied by slicers.
Ignores all filters except the column specifically mentioned
ALLEXCEPT
in the argument
REMOVEFILTERS Removes filters from a column or table.
KEEPFILTERS Keeps existing filters while applying new ones.
🔹 Example:
Total Sales (Ignoring Filters) = CALCULATE(SUM(Sales[Revenue]), ALL(Sales))
CALCULATE & Context Functions (Most Common in Interviews)
Function Description
CALCULATE Modifies the context of a calculation.
CALCULATETABLE Returns a table modified by filters.
VALUES Returns a single column table of unique values.
SELECTEDVALUE Returns the value selected in a filter or slicer.
🔹 Example:
Total Sales for 2024 = CALCULATE(SUM(Sales[Revenue]), Sales[Year] = 2024)
Time Intelligence Functions (Most Asked in BI Roles)
Function Description
SAMEPERIODLASTYEAR Returns the same period in the previous
Function Description
year.
PREVIOUSYEAR /
Returns the previous year or month.
PREVIOUSMONTH
DATESYTD / DATESMTD / Calculates Year-To-Date, Month-To-Date, and
DATESQTD Quarter-To-Date values.
TOTALYTD / TOTALMTD / Aggregates Year-To-Date, Month-To-Date,
TOTALQTD and Quarter-To-Date values.
Shifts the date context (e.g., move forward
DATEADD
or backward by days, months, years).
🔹 Example:
Sales Last Year = CALCULATE(SUM(Sales[Revenue]),
SAMEPERIODLASTYEAR(Sales[Date]))
Logical & Conditional Functions (For Data Cleaning & Manipulation)
Function Description
IF / SWITCH Conditional logic (like IF-ELSE).
ISBLANK Checks if a value is blank.
COALESCE Returns the first non-blank value.
🔹 Example:
Category = IF(Sales[Revenue] > 1000, "High", "Low")
Relationship & Table Functions (Advanced-Level Questions)
Function Description
RELATED Fetches data from a related table.
RELATEDTABLE Returns all rows from a related table.
LOOKUPVALUE Retrieves a value from another table based on a condition.
CROSSJOIN Returns all possible combinations of two tables.
🔹 Example:
CustomerRegion = RELATED(Customer[Region])
Ranking & Windowing Functions (Used in Advanced BI Reports)
Function Description
RANKX Ranks values within a table based on a given expression.
TOPN Returns the top N values based on a measure.
🔹 Example:
Rank = RANKX(ALL(Sales), SUM(Sales[Revenue]), , DESC)
FUNCTIONS to read later
TREATAS
DATEDIFF
DATESINPERIOD
VALUES Function
for table
Use aggregator like max(), count() if you want to use it in measure
