Stairway to DAX and Power BI - Level 16: The DAX ALLEXCEPT() Function (2020-10-14)

 SQLServerCentral Newsletter for October 14, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to DAX and Power BI - Level 16: The DAX ALLEXCEPT() Function]( - [What is Oracle ASM to Azure IaaS?]( - [Free eBook: SQL Server Execution Plans, Third Edition]( - [From the SQL Server Central Blogs - Exporting Environment Variables out of the SSIS Catalog]( - [From the SQL Server Central Blogs - Getting Your Employer to Pay for the PASS Summit]( Question of the Day - [Removing a dataframe column in R]( The Voice of the DBA  Daily Coping Tip Do something to overcome an obstacle you are facing 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. Bean Counting to Burnout I was listening to a story about healthcare in the US recently. This was an interview with Vivian Lee, the author of [The Long Fix](, which proposes some ways to improve the system under which I live. I thought it was a good interview, and I am looking forward to reading the book. There was one stretch in the interview that caught my ear, and that was on the struggles of many modern doctors in the US to avoid burnout. A part of the reason for this is the amount of paperwork they must complete, which takes away from many of the reasons they got into practicing medicine. I know that many people in IT professions work long hours. We have lots of deadlines to meet, on call responsibilities, and the pressures to ensure that our systems work well for customers and clients. We don't necessarily have the upside that doctors do, so working harder doesn't necessarily lead to more compensation. On top of the work we do, there seem to be no shortage of meetings in many companies, though I don't hear too many complaints about paperwork. However, with the move to more OpEx and cloud services, I wonder if we will have to deal with more paperwork in the future, more reporting, and more accountability for the financial side of our departments. I would like to think that we will continue to work with technology most of the time, and I hope that after this pandemic that most managers will cut down on meetings and start to trust more people to get work done without oversight. We certainly have enough of a risk of burnout without the added burden of unnecessary meetings and paperwork. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to DAX and Power BI - Level 16: The DAX ALLEXCEPT() Function]( Bill Pearson from SQLServerCentral Business Intelligence Architect, Analysis Services Maestro, and author Bill Pearson introduces the DAX ALLEXCEPT() function, discussing its syntax, uses and operation. He then provides hands-on exposure to ALLEXCEPT(), focusing largely upon its most popular use in removing filters from all columns in a table - except the filters we specify. [What is Oracle ASM to Azure IaaS?]( Additional Articles from SimpleTalk Kellyn Pot'Vin-Gorman explains Oracle Automatic Storage Management and why it’s impossible to do a comparison to Azure IaaS. [Free eBook: SQL Server Execution Plans, Third Edition]( Press Release from Redgate If a query is performing poorly, and you can't understand why, then that query's execution plan will tell you not only what data set is coming back, but also what SQL Server did, and in what order, to get that data. It will reveal how the data was retrieved, and from which tables and indexes, what types of joins were used, at what point filtering, sorting and aggregation occurred, and a whole lot more. These details will often highlight the likely source of any problem. From the SQL Server Central Blogs - [Exporting Environment Variables out of the SSIS Catalog]( Koen Verbeeck from Koen Verbeeck Sometimes when you’ve created a SSIS catalog, along with folders, environments and environment variables, you want this content on another server. Unfortunately, you can only script out these objects... From the SQL Server Central Blogs - [Getting Your Employer to Pay for the PASS Summit]( Andy Warren from SQLAndy It’s just over 30 days to the 2020 PASS Summit and that is plenty of time to get the boss to say yes, but you have to ask! I...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Removing a dataframe column in R I have this simple dataframe in R: > mysales orderdate customerids orderqty orderprice linetotal 1 2019-09-23 1 10 10 100 2 2019-06-16 2 20 10 200 3 2019-03-09 3 30 9 270 I run this: mysales$linetotal <- NULL What is the result? 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) Backup Security In order to secure my backup files in Azure Blob Storage, I need to ensure that my instance can authenticate. What do I need to set up security for my backup command to send the data to Azure Blob Storage? Answer: A credential is used that was created with the keys or tokens. Explanation: A credential is used. The account and key, or SAS and token are used when creating the credential. Ref: SQL Server Backup to URL - [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 [Log File Growing]( - Hi, We have a database that has 50GB of data, 99% of which is in one table. In this table there are 31,004,447 records, the developer is running a query to remove all records older than 15 days. This query should delete all the records as there are no records less than 15 days old. […] SQL Server 2017 - Development [aggregate value by group]( - Hi, I have the following table: create TABLE test ( [Claim ID] int NOT NULL, [Damage ID] int NOT NULL, [Location ID] int NOT NULL, [Seq Num] int NOT NULL, [Reserve Date] datetime NOT NULL, [Reserve amt] Decimal(18,2) NOT NULL ) insert into test select 668835, 631560, 36225627, 1, '06-09-2014', 20000 UNION ALL select 668835, […] SQL Server 2016 - Administration [Alerts/Notifications to External Email Address]( - We have a new SQL Server 2016 setup that we are migrating our SQL Server 2008 into. We do not have in house SQL admins so we have a consultant doing this work for us. I have an odd behavior with email. I am able to test the Database Email to my work email, and […] SQL Server 2016 - Development and T-SQL [Query]( - How to show below result in sql ID=205&SID=1005&TID=3 ID=25&SID=1005&TID=3 ID=2&SID=1005&TID=3 ID=2006&SID=1005&TID=3 Result 205 25 2 2006    Administration - SQL Server 2014 [Lost relationships]( - This seems kind of weird, and I've never even heard of it happening to anyone, but it happens to me fairly regularly - SQL Server loses the relationships between some tables. I'm newly using 2014, and used 2008 R2 for many years previously. It happens in both. I have no idea when or why, but […] [Sudden PLE drop]( - Hi Experts,  We are observing sudden drop in PLE. Buffer cache rit hatio, check point pages/sec ,lazy writes/ sec are all normal. I tried running trace to find if any query causing issue but didnt find any. The DB is getting logged from BizTalk servers. Can you guide me on how to troubleshoot this? SQL Server 2012 - T-SQL [Create a view...]( - hello, i have a customer table and than a customer group group table which is basically grouping customers Than i have a table price like: cust1 item1 price cust2 item2 price cust3 item2 price cg1 item5 price where cg1 is a group and has cust1 and cust2. I need to create a view with the […] SQL Server 2019 - Administration [Multi-Server Maintenance Plans in SQL Server 2019]( - Hello all! I hope you're all staying safe and well! So I've set up Multi-Server Administration in my environment so that I can just quickly add new target servers to my already created Maintenance Plans. I've created a Multi-Server Maintenance Backup plan to backup user databases nightly. However, it doesn't seem to be intelligent enough […] SQL Server 2019 - Development [Help me develop my skills]( - Hi Guys, I'm hoping for a little advice and please delete if this is not relevant here. I've been involved in Data analytics and MI reporting for quite some time but predominately I have used SSMS, Excel, Access as my main tools. I'm very keen to upskill and I was wondering if someone could give […] Reporting Services [SSRS Optional Parameters]( - Is there a way to have optional parameters without having the parameter property set to Allow null value? In other words I have a report that they want to pick either by Month and Year parameter or Begin Date and End Date Parameter. [Using Text Boxes, no table, with more than one data row.]( - Good morning. I'm not really sure how to write the title so let me explain my issue. I have a two page SSRS report with Text boxes and Expressions. It is a summary of benefits and pay for an individual employee and works great. I used Text boxes because of the layout, how it appears, […] Integration Services [SSISDB Encryption - Password not available, Master Key not open]( - Hi all, I now have an instance of SQL Server 2019 where the SSISDB database that holds the SSIS Catalog, does not have the master key open, and we don't have the password.  There's no way to get the password, as the only personnel that might have known it have departed. Thus while I can […] Security and Auditing [TDE and Transaction Logs]( - Howdy, Question: Is it possible to decrypt a Transaction Log taken off a database against which TDE is applied? Context: Business is transferring a portion of functionality to an outsourcer, this requires us to shift a substantial quantity of SQL data in a short timeframe. We'd like to use a delta approach to minimise the […] Employers and Employees [short & part time contracts]( - So I'm interested in finding short term contracts or the kind of work where I can be on call for issues & upgrades on a contract basis or a couple of days a week. Over the course of my career I have found that a full time DBA or developer is often not required and […] Microsoft Access [ACCESS Expression convert to SQL]( - Need help on writing an expression created in ACCESS in SQL. I am new to SQL so bare with the long written queries. I think I need everything spelled out so I can get a good grasp on the basics. I am at a lost with trying to figure out how to even begin re-writing […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -