DAX for SQL Folks: Part III - DAX Calculations (2020-06-22)

 SQLServerCentral Newsletter for June 22, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [DAX for SQL Folks: Part III - DAX Calculations]( - [Testing SQL Server Stored Procedures and Functions with SQL Data Compare]( - [Improve Row Count Estimates for Table Variables without Changing Code]( - [From the SQL Server Central Blogs - Azure Platform Series: How to setup Azure Cloud Shell]( - [From the SQL Server Central Blogs - Backup your SQL instances configurations to GIT with dbatools – Part 2 – Add parallelism]( Question of the Day - [Memory Grant Feedback]( The Voice of the DBA  Daily Coping Tip Appreciate the joy of nature and the beauty in the world around you. I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Open Source is Not a Career Path This editorial was originally published on February 3, 2005 editorial. Steve is on vacation, but do you think the world of Open Source has changed? I think it certainly has. Linus Torvalds, the founder and father of Linux said this week that "Open Source is not a career path" at the ODSL's summit. That's funny, because I think most of you reading this and many people that are devout Linux users are thinking exactly that they can make a career of open source. I've seen many open source writings about the ways you can make things work with examples such as Linus, the authors of Apache, the folks that wrote the Berkley-db, etc. and make a living. But most of these examples are people that are great programmers or technical individuals, the sort of which are rare and would probably succeed in many of their endeavors. For the average Joe or Jane out there, we do need a career path. We are people that are happy to work and earn our money, but we're not the brightest of best stars in our areas, just competent or even very skilled individuals. For many of us, professionalism is the way and reason that we work. We take pride in what we do, and want to do well, but we don't do it for some ideal. And many of us aren't interested in continually lobbying our management that they should be spending resources and money on that ideal. I'm sorry, I don't believe all software should be free. If people were more honest and upstanding in how they deal with code, and maybe a little less greedy, then maybe I'd be more for open source, but I think that closed source, proprietary code has a place in this world. I think programmers should be paid for their time as should companies. I think things can go too far in any direction and I'm not a huge fan of some of Microsoft's tactics to keep their platform closed or force upgrades, but I do feel that they have done lots to bring computing to the masses. I know I'm walking the middle here, but that's how I feel. I like the idea of open source. I've learned many things from examples and other people's code, but I've also written some things that I don't want to give away for free. Nothing in this world is free. We all pay in some way. Free TV has commercials, free giveaways from a store or radio station have some marketing appeal, free newspapers and other media rely on advertising, which in turn raises the prices we pay for everything. Free is non-existent and in many cases I think the Internet is conditioning too many of our younger people that things should be free as in beer when the intent was always free as in speech. Steve Jones Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [DAX for SQL Folks: Part III - DAX Calculations]( Frank A. Banin from SQLServerCentral Introduction In response to my approach in the pop... [Testing SQL Server Stored Procedures and Functions with SQL Data Compare]( Additional Articles from Redgate Phil Factor demonstrates a cunning way to test stored procedures or functions, such as after refactoring, by storing the 'before' and 'after' results in views and then using SQL Data Compare to spot any discrepancies. [Improve Row Count Estimates for Table Variables without Changing Code]( Additional Articles from SimpleTalk Table variables can cause performance issues with joins when they contain a large number of rows. In SQL Server 2019, Microsoft has improved how the optimizer works with table variables which can improve performance without making changes to your code. In this article, Greg Larsen explains how this feature works and if it really does make a difference. From the SQL Server Central Blogs - [Azure Platform Series: How to setup Azure Cloud Shell]( HamishWatson from The Hybrid DBA's Blog This blog post will be a series on the Azure Platform and some things we can use it for. Background: I run a consultancy company – and sometimes I... From the SQL Server Central Blogs - [Backup your SQL instances configurations to GIT with dbatools – Part 2 – Add parallelism]( Cláudio Silva from Cláudio Silva We have seen how we can export and save the results to a folder and commit them to a GIT repository on my last blog post Backup your SQL...   Question of the Day Today's question (by Greg Larsen):  Memory Grant Feedback Memory grant feedback tunes the memory requirements for batch mode queries beginning in 2017 and row mode queries beginning in 2019. In which situations will this feature be helpful? (More than one answer is correct) Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Adding Extended Properties to Tables I want to add an extended property to the dbo.Adverts. What parameters do I need to specify to sp_addextendedproperty? Answer: name, value, level0 type of schema, level0 value of dbo, level1type of table and level1name of dbo.Adverts. Explanation: You can ommit lower level types and values, but not higher level ones. In this case, the table is at level 1, so level 0 must be specified. The correct values at level 0 are for the schema and dbo. Level 1 is the table and Adverts. Ref: sp_addextendedproperty - [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 - Administration [SSMS: How to save multiple resultsets from same query into 1 result or 1 file?]( - I am submitting an official request for DBA of certain servers to run a script on those servers and give me back the resultset. However the script (the actual script is 3 times larger than the attached and returns 20 more results) returns multiple results, so a DBA will not be copy/pasting each resultset into […] [Availability Groups for failover AND mirror]( - I've got a 2-node failover AG setup. I want to add a 3rd node to be used as a reporting server. That could be read-only, but has to be available all the time. For reasons beyond my control, I can't use replication. The database has no primary keys indexes (3rd-party software design). So, it all […] SQL Server 2017 - Development [SQL Server Fragmentation- Defragment]( - The table consist of around 2 billion record on an average there are 1000K inserts happening daily , we see some slowness in recent days , so while checking the table we saw some fragmentation information. Above is the query i used against my dataware database and observed some fragmentation in the index for some […] SQL Server 2016 - Administration [Creating DB diagram]( - Hi, I was planning to create a DB diagram of a user DB to better understand the relationship however when I right-click on database diagram (please expand the user DB to see this option) I see an alert window saying 'This database does not have one or more of the support objects required to use […] [SQL server 2016 SP2 error]( - Hi All,  am trying to update my 2016 SQL server instance to the SP2. the current version is Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64) Jun 15 2019 19:20:12 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 18363: ) SP2 setup file : SQLServer2016-KB4549825-x64.exe for some […] [SQL Trace Not Capturing Database Events....EXEC, INSERT, UPDATE...]( - I want to see what Stored Procedures are run when users enter data in a 3rd party product we use. I would also like to see INSERTS & UPDATES to tables. I set up a trace that I have used elsewhere with success, but in this case, it's not capturing any of the events I […] SQL Server 2016 - Development and T-SQL [Help with T-SQL]( - I have a web interface for managing tool rentals for our chapter members. I have a display page that will list all of the tools we have and to flag those tools that are currently being rented out to include the expected return date. The important thing is to know which are available and which […] [How to optimise the table performanace (90 lacs records)]( - Hi, I have a table with 5000 users along with 6 years of attendance data. If I want to retrieve user data from the table it takes time to load. Table Structure Usercode, Rosterdate, Shift code, AttendanceCode Case 1 If i want to retrieve the shift information from 1-jun-2020 to 10-jun-2020 for 5000 users, would […] [Help in Date spans for continuoes and regular dates spans]( - Hello all, I would like to take input scripts and generate output data as given below. output is expected as if member is continuously enrolled without any gap in enrollment get min and max and if there is enrollment gap leave as is. for any given member there are no multiple spans just leave as […] Administration - SQL Server 2014 [OPENQUERY giving wrong result on linked server]( - I have setup linked servers on my SQL Server and choose Oracle as a linked server. SOMETIMES, When I run the query below under the SA username, I get different result. However when I run under my own username I get correct result. I Can't understand this anomaly. The below query returns only one row. The difference occurs in […] [Sum of all Memory Clerks and Total Server Memory (KB)]( - I am trying to understand the memory management of SQL Server. I have a Windows Server 2019 on virtual environment with 64 GB of RAM. I also have SQL Server 2019 installed with Max Server Memory 59776 MB. By using the following query I found the list of all memory clerks: SELECT [type] AS [ClerkType], […] SQL Server 2012 - T-SQL [? ON Parsing an XML Field]( - Hi, I know I've asked this before and got a good link , but can't figure out how to parse this XML field. I would usually parse something like Data.Value('Data/.../..) But not sure how to parse out say "Case" from the field below(SqlParameters)? SQL Server 2019 - Administration [How do I count the objects (tables,views,indexes) for a database?]( - Is there any simple script which I can use to count the user objects in a database? Reason is, I'm planning to do an upgrade from SQL 2008R2 to SQL 2019 & my plan is to take a count of the objects on the old SQL and 2019 so I can compare and see if […] Anything that is NOT about SQL! [SSC Traffic Declining ?]( - Are there fewer posts & replies on SSC these days ? It seems like it. Or are they just spread over more sub forums of the various SQL versions ? COVID-19 Pandemic [Daily Coping 16 Jun 2020]( - Today’s tip is to find the joy in music today: sing, play, dance, or listen.   [RSS Feed]([Twitter]( 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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -