DAX

[PowerBIDAXProb]

Hello experts,

Need few clarifications regarding DAX functions:

1. MAX vs SELECTEDVALUE -- which is better to use? 

2. Is it better to compare DateKey(20250531) format or date format in measures? (a < DateKey or a < fullDate.. which will be faster??)

3. Shall I create a separate measure with SELECTEDVALUE or MAX and then refer it in all the dependent measures? Or use the function in variable for every measure?

I know, on a single measure, It won't affect much. But, when the same change is done in 40-50 measures, I believe it can impact the performance

[bhanu_gautam · Accepted solution]

@PowerBIDAXProb 

MAX vs SELECTEDVALUE: The choice between MAX and SELECTEDVALUE depends on the context of your data and what you are trying to achieve. MAX returns the maximum value in a column, which is useful when you need the highest value. SELECTEDVALUE returns the value when there is only one value in the context, otherwise it returns a specified alternate result (or BLANK if not specified). SELECTEDVALUE is often used in scenarios where you expect a single value in the context and want to handle cases where there might be multiple values more gracefully. If you are certain that there will always be a single value in the context, SELECTEDVALUE can be more intuitive and safer to use.

Comparing DateKey vs fullDate: Comparing a numeric DateKey (e.g., 20250531) is generally faster than comparing full date formats because numeric comparisons are less computationally intensive than date comparisons. However, the performance difference might be negligible unless you are performing a very large number of comparisons. Using DateKey can also simplify the logic and improve readability if your data model is designed around these keys.

Creating a separate measure vs using the function in a variable: Creating a separate measure with SELECTEDVALUE or MAX and then referring to it in all dependent measures can improve performance and maintainability. This approach avoids redundant calculations and makes your DAX code cleaner and easier to manage. Using variables within each measure can also be beneficial for performance, as it ensures that the calculation is performed only once per evaluation context. However, if the same logic is used across multiple measures, defining a separate measure is generally the better approach.

[FBergamaschi · Accepted solution]

1. There in no optimal choice as they are different. MAX returns the maximum value among many values visibile in the filter context, SELECTEDVALUE returns either the only value visibile in the filter cotext or blank (or a chosen default value you can set when there are multiple values visibile)

2. Date is better if DateKey(20250531) is a text otherwise if it is a number DateKey(20250531) is better as numbers are always fastwer to manage for keys. Avoid please datetime unless really needed

3. Surely better separate measures and their reuse

[Poojara_D12 · Accepted solution]

Hi @PowerBIDAXProb 

Your questions touch on important DAX performance and modeling practices, especially when working with larger datasets and multiple interdependent measures. First, when choosing between MAX and SELECTEDVALUE, the key difference is that MAX returns the highest value in the current filter context, even if multiple values exist, while SELECTEDVALUE returns a value only if there's a single distinct value, otherwise it returns blank or a default. In most cases, if you’re expecting a single value (like a date selected via slicer), SELECTEDVALUE is safer as it helps catch unexpected multiple selections; however, MAX can be better when you're intentionally aggregating or want to tolerate multi-selection contexts. Second, comparing an integer-based DateKey (like 20250531) versus a full Date column can have minor performance differences. In general, comparing integers is slightly faster than comparing date values, especially in large models, but the difference is often negligible unless done inside expensive row iterations. Still, many teams prefer using DateKey in fact tables for performance and simplicity, particularly in star schema designs. Third, in terms of performance and maintainability, it's usually a good idea to centralize commonly used expressions—like a selected date or threshold—into separate helper measures rather than repeating logic across 40–50 other measures. This approach improves readability, reduces maintenance effort, and may benefit query plan reuse, though the performance difference might be minimal unless the logic is complex or involves context transitions. So while performance gains might be small individually, your instinct is right: in large-scale models with many measures, consistent and centralized logic can reduce redundancy, improve DAX maintainability, and lead to more stable performance over time.

[v-kpoloju-msft]

Hi @PowerBIDAXProb,

Thank you for reaching out to the Microsoft Fabric Community, and thanks to @FBergamaschi, @bhanu_gautam, for their valuable inputs on this thread.

Both functions serve different purposes. SELECTEDVALUE is ideal when you expect only a single value in the filter context (such as a slicer), as it returns that value or blank/default otherwise. MAX, on the other hand, is more robust when multiple values may exist—it returns the highest value in context and avoids blanks. So, the better option depends on the use case, but from a reliability standpoint, MAX is generally safer when the context isn’t guaranteed to be single valued. SELECTEDVALUE function - DAX | Microsoft Learn
Use SELECTEDVALUE instead of VALUES in DAX - DAX | Microsoft Learn

Using DateKey (an integer) for comparisons is typically more efficient than using full date formats, especially in large models, as numeric comparisons are faster and lighter on the engine. However, full date formats are necessary when working with DAX time intelligence functions like DATESYTD or SAMEPERIODLASTYEAR. A good practice is to maintain both columns in your Date table use DateKey for joins and filters and use full dates for calendar logic. MAX function (DAX) - DAX | Microsoft Learn

If the same logic (like SELECTEDVALUE(Date[Month])) is used across many measures, creating a reusable base measure improves maintainability and consistency. However, for performance-critical scenarios or when the value is used only within a specific measure, defining it as a VAR within the measure can be more efficient. Since you're working with 40–50 measures, reusing shared expressions where applicable is helpful but always validate with Performance Analyzer or DAX Studio if performance is a concern.

If you have any questions regarding this, please feel free to reach out to us. We will be happy to help you. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.    

[v-kpoloju-msft]

Hi @PowerBIDAXProb,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @PowerBIDAXProb,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @PowerBIDAXProb,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[FBergamaschi · Accepted solution]

1. There in no optimal choice as they are different. MAX returns the maximum value among many values visibile in the filter context, SELECTEDVALUE returns either the only value visibile in the filter cotext or blank (or a chosen default value you can set when there are multiple values visibile)

2. Date is better if DateKey(20250531) is a text otherwise if it is a number DateKey(20250531) is better as numbers are always fastwer to manage for keys. Avoid please datetime unless really needed

3. Surely better separate measures and their reuse

[bhanu_gautam · Accepted solution]

@PowerBIDAXProb 

MAX vs SELECTEDVALUE: The choice between MAX and SELECTEDVALUE depends on the context of your data and what you are trying to achieve. MAX returns the maximum value in a column, which is useful when you need the highest value. SELECTEDVALUE returns the value when there is only one value in the context, otherwise it returns a specified alternate result (or BLANK if not specified). SELECTEDVALUE is often used in scenarios where you expect a single value in the context and want to handle cases where there might be multiple values more gracefully. If you are certain that there will always be a single value in the context, SELECTEDVALUE can be more intuitive and safer to use.

Comparing DateKey vs fullDate: Comparing a numeric DateKey (e.g., 20250531) is generally faster than comparing full date formats because numeric comparisons are less computationally intensive than date comparisons. However, the performance difference might be negligible unless you are performing a very large number of comparisons. Using DateKey can also simplify the logic and improve readability if your data model is designed around these keys.

Creating a separate measure vs using the function in a variable: Creating a separate measure with SELECTEDVALUE or MAX and then referring to it in all dependent measures can improve performance and maintainability. This approach avoids redundant calculations and makes your DAX code cleaner and easier to manage. Using variables within each measure can also be beneficial for performance, as it ensures that the calculation is performed only once per evaluation context. However, if the same logic is used across multiple measures, defining a separate measure is generally the better approach.