Stairway to DAX and Power BI - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions (SQLServerCentral 1/9/2019)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Stairway to DAX and Power BI - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions]( - [Free eBook: Inside the SQL Server Query Optimizer]( - [Getting Started with GitHub for SQL Server Developers]( - [Testing SQL in the Advent of Code]( (From the SQLServerCentral Blogs) - [SQL Server Removing Duplicates]( (From the SQLServerCentral Blogs) The Voice of the DBA Is Failure Allowed? One of the things that I often hear from Developers working in DevOps or lean, agile type environments is that failure must be tolerated, or even encouraged. This is important in building a [culture of experimentation and learning](. We need to try new things, and if they work well, teach others and expand their use. Not every experiment will succeed, so we want to fail fast and early, and also ensure that others learn from our experience to avoid repeating that particular failure item. Is that the case for DBAs? Are we allowed to experiment and fail? I'd like to think so, though in some ways we need to be more careful with how we experiment. Since the database is that place where we have lots of data and information, our failures can be much more widespread and impactful than a new feature or function not working in software. Certainly the potential problems of exposing lots of data to unauthorized users is a problem. As an example, many of us find databases that have poor security models set up. Whether we have "sa" being used by applications or ad hoc permissions added to random accounts, there are better ways to implement data protection. Just changing the permissions for objects, however, isn't the type of experiment I'd run. Instead, I'd look to add a new user account with no permissions and slowly experiment with adding that account to a new role, starting to assign new permissions and determining if this meets the needs of other users. This is the same type of experiment I might conduct with a schema that I need to evolve. I'd make a copy, move some data, perhaps in a second database where I use views, synonyms, and other objects that can help me understand the impact of making design changes on my application. If I've captured the scripts in a VCS (perhaps a branch), and documented the changes, I could easily repeat this in the future in my production database, knowing the steps to follow that will minimize disruptions. As a DBA, I've often been very conservative and careful in how I approach the administration of databases, which includes the deployment of schema changes. Unlike many other DBAs, I haven't viewed my goals as an impediment for developers, but rather a challenge to get work done in a save way. This might include trading time and space to limit disruptions, but always ensuring that we can get enhancements to customers, whether they realize it or not. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [SQL Change Automation]( CI/CD for your SQL Server database Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control. [Try it free]( [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more]( Featured Contents  [] [Stairway to DAX and Power BI - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions]( Bill Pearson from [SQLServerCentral.com]() As a part of his "Function / Iterator Pairs" mini-series, Business Intelligence architect, Analysis Services Maestro, SQL Server MVP, and author Bill Pearson introduces the DAX MAX() and MAXX() functions, discussing similarities and differences. He then provides some hands-on exposure to the use of each, particularly in combination with other DAX functions, in generating "largest numeric values" to meet differing needs within our PowerPivot model designs.[More »](Series/120833/) ---------------------------------------------------------------  [] [Free eBook: Inside the SQL Server Query Optimizer]( Press Release from [SQLServerCentral.com]() This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way.[More »]( ---------------------------------------------------------------  [] [Getting Started with GitHub for SQL Server Developers]( Additional Articles from [MSSQLTips.com]() Learn some of the basics of GitHub and how this can be utilized for your SQL Server development projects.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Testing SQL in the Advent of Code]( Steve Jones from [SQLServerCentral Blogs]( I like participating in the Advent of Code each year, though my participation often varies wildly as life gets in...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [SQL Server Removing Duplicates]( Gavin Draper from [SQLServerCentral Blogs]( I know, I know your data could never possibly have duplicates because obviously, you have all the constraints in place...[More »]( Question of the Day Today's Question (by Steve Jones): I have this code in Python. What is returned? positions = "S", "OH", "MH", "OPP", "OH", "L" positions[:-2] Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: Python. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Expert T-SQL Window Functions in SQL Server]( Expert T-SQL Window Functions in SQL Server takes you from any level of knowledge of windowing functions and turns you into an expert who can use these powerful functions to solve many T-SQL queries. Replace slow cursors and self-joins with queries that are easy to write and fantastically better performing, all through the magic of window functions. [Get your copy from Amazon]( today. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): Which filesystems on Linux are supported for SQL Server 2017 on Linux? Answer: XFS and ext4 Explanation: SQL Server 2017 supports XFS and ext4 only. Ref: SQL Server on Linux - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Administration]( [Availability group listener is not listening]( - I've successfully created an availability group with 2 nodes that one node is physical and another is VM. Also running... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [substring with Charindex]( - Hello. I have a COLUMN that I'm trying to break apart into separate fields. This column is delimited by a... [SQL Quiz]( - Removed as per the wish of a user ! --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Transaction log filling up issue.]( - Hi, I am seeing a bit of a strange issue on a lot of servers in the place I am currently... [Database in RECOVERY PENDING state - Access Denied]( - Hi I have s SQL2016 instance with 8 out of 10 databases in recovery pending state and I need assistance. I had... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Writeback Values bigger than Int allows?]( - Ahoi, i have a write Table enabled and fully functionally working, the only issue is my users need to write back... [Dynamic creation of an ASCII "banner"]( - I've been searching around the Internet and have not found anything for this, therefore it is possible that it does... [Storing a hash of a row]( - Hi Folks, First of all, happy new year to you all! TL;DR - I need to store a calculated hash value from a... [Sample TSQL , SSIS , SSRS projects]( - Hi Guys, Where can I get sample projects TSQL , SSIS , SSRS projects. that I can provide for students? Thanks --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Alternate Way Of Writing the Query]( - Hello Friends, We have been looking to optimize a query which at times run erratic and keeps changing its execution plan.... [Cross compare from within the same table.]( - I have a list of items with a substitute and a Y/N box to determine that they are old items.... --------------------------------------------------------------- [SQL Server vNext]( : [SQL Server 15 - Development]( [insert with output clause]( - I have an insert with an output clause where one of the outputted columns is not in the inserted list,... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Select convert to 2 decimal places, comma, right aligned]( - Hello The field below currently has an output that looks like this - select A. as from EQSA0345 A Replacement Cost 523.88000 60689.76000 48860.26000 77239.26000 132227.26000 65668.18000 71523.34000 I would... --------------------------------------------------------------- [Cloud Computing]( : [SQL Azure - Development]( [Renaming a primary key columns in the Azure DB]( - Summary: I have a DB that has combined keys as proimary keys and I have a task to rename one of... --------------------------------------------------------------- [Programming]( : [XML]( [Problem reading all Attributes into SQL table]( - Hello everyone. I'm writing a proc that imports several XML files and stores them in a SQL table.  Here's a sample... --------------------------------------------------------------- [SQL Server 2005]( : [Backups]( [Restore sql server backup]( - While restoring a database backup file taken in SQL 2005 Standard edition to new database in SQL 2014 Standard edition;... [Maintenance clean-up task completing, but giving Error: 18204,Severity: 16, State: 1]( - I have a maintenance plan that runs a cleanup task on the local server to delete all transaction files older... [Can you recover data from transaction log?]( - Hi, I have question regarding data recovery. Yesterday, our Navision consultants accidently deleted important business data from our database. We were... --------------------------------------------------------------- [SQL Server 2005]( : [CLR Integration and Programming.]( [Translate Sql Query to Clr]( - Hello, How can I write the following query in c# clr ? CREATE FUNCTION SQL_HT ( @param1 INT, @param2 INT)RETURNS@tablo TABLE(  colon1... --------------------------------------------------------------- [Career]( : [Employers and Employees]( [Evolution of career from perm/consulting to freelancing?]( - Hi,  I am working as DBA for 17+years.  permanent -> consultant->  permanent -> consultant ...  at some point I start thinking about... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com