← 30 Days Power BI Series
Day 9 of 30IntermediateDAX

Day 9: DAX — Intermediate Functions

5 questions · Power BI Interview Preparation

Q1

How does RANKX work in DAX?

RANKX(table, expression, [value], [order], [ties]) ranks rows in a table based on an expression. Example for ranking products by revenue: Product Rank = RANKX(ALL(Products), [Total Revenue], , DESC, DENSE) ALL(Products) creates the full list to rank against. [Total Revenue] is the measure to rank on. DESC means highest value = rank 1. DENSE means tied values get the same rank with no gaps (1,1,3) vs SKIP (1,1,3 with no rank 2). Used in visuals to show "Top 5 Products" or "Sales Rep Leaderboard".

💡 Interview tip: RANKX with ALL is the pattern for ranking across all items regardless of visual filters. Without ALL, it would only rank within the current filter context — usually not what you want.
Q2

What is HASONEVALUE and when do you use it?

HASONEVALUE(column) returns TRUE if the current filter context has exactly one value for that column — useful for showing a single value when a slicer has one item selected, or showing a message when multiple items are selected. Common use: dynamic titles: Report Title = IF(HASONEVALUE(Region[Region]), "Revenue Report — " & VALUES(Region[Region]), "Revenue Report — All Regions"). Another use: prevent meaningless calculations when too many values are selected for a measure to make sense.

💡 Interview tip: HASONEVALUE is the DAX way to detect single-selection in a slicer. Combine it with VALUES() to extract the selected value as text.
Q3

What is SWITCH in DAX and how is it used?

SWITCH(expression, value1, result1, value2, result2, ..., [else]) is the DAX equivalent of a multi-branch IF or CASE statement. Much cleaner than nested IF for multiple conditions. Example: Quarter Label = SWITCH( QUARTER('Date Table'[Date]), 1, "Q1 (Jan-Mar)", 2, "Q2 (Apr-Jun)", 3, "Q3 (Jul-Sep)", 4, "Q4 (Oct-Dec)" ) SWITCH(TRUE(), ...) is used for range-based conditions: SWITCH(TRUE(), [Revenue] > 100000, "High", [Revenue] > 50000, "Medium", "Low").

💡 Interview tip: SWITCH(TRUE(), ...) is the most flexible pattern — it evaluates each condition in order and returns the first TRUE result. Use it instead of deeply nested IF statements.
Q4

How do you write a running total measure in DAX?

Running Total Revenue = CALCULATE( SUM(Sales[Revenue]), FILTER( ALL('Date Table'[Date]), 'Date Table'[Date] <= MAX('Date Table'[Date]) ) ) This returns the cumulative sum of revenue from the earliest date to the current date in context. MAX('Date Table'[Date]) gets the last date in the current filter context, and FILTER(...ALL...) includes all dates up to that point. Use in a line chart with Date on the axis to show cumulative growth over time.

💡 Interview tip: Running totals require ALL to override the date filter and then re-filter to only dates up to the current one. This is a classic DAX pattern — understand the logic, not just the formula.
Q5

What is SELECTEDVALUE in DAX?

SELECTEDVALUE(column, [alternate_result]) returns the value of a column when exactly one value is in the filter context, otherwise returns the alternate result. Cleaner than the IF(HASONEVALUE(col), VALUES(col), "Multiple") pattern. Common uses: dynamic measure titles that show the selected slicer item, capturing a user's selection to use in a calculation, building what-if parameters. Example: Selected Region = SELECTEDVALUE(Region[Region], "All Regions") Used in a card visual with a slicer — shows the currently selected region name or "All Regions" when nothing is selected.

💡 Interview tip: SELECTEDVALUE is a Power BI-specific function (not in Analysis Services). Use it for dynamic titles and user-selection-aware measures.
← Day 8All DaysDay 10 →

Want live Power BI coaching?

Join EVIKA Academy for hands-on Power BI training with real projects and placement support in Delhi NCR.

Book Free Demo →
Best Data Analytics Course in Noida Delhi NCR | EVIKA ACADEMY