Stairway to DAX and Power BI - Level 16: The DAX ALLEXCEPT() Function (2021-09-24)

SQLServerCentral Newsletter for September 24, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Create an RPA Flow that Connects to Oracle Data in UiPath Studio]( - [Stairway to DAX and Power BI - Level 16: The DAX ALLEXCEPT() Function]( - [How LAG compares to other techniques]( - [From the SQL Server Central Blogs - Slides and Video from Building a Regret-free Foundation for your Data Factory Now Available]( - [From the SQL Server Central Blogs - T-SQL Tuesday #142–What is Bicep?]( Question of the Day - [The OPENJSON return schema]( The Voice of the DBA  Daily Coping Tip Walk away from your desk for 5 minutes. Find a quiet place and just be. 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. Thank a Mentor The delicate balance of mentoring someone is not creating them in your own image, but giving them the opportunity to create themselves.” — Steven Spielberg I saw [that quote posted on Twitter]( with the request that people tag a mentor that had helped them. Since then, a few people have posted their thanks to someone, and I've been tagged a few times. I'm honored that people think I've helped them somehow, as that's been a portion of my career for the last twenty years as I make an effort to do so. I'm not looking for lots of people to post anything about their mentors here, but I am asking that you stop and thank someone that has helped you in your career. Let them know they made a difference and how they did so. It's good for them and for you. We all need help, support, and encouragement at times to help us move forward. We can all use someone that can give us an independent view of our decisions, our choices, and more, helping us keep perspective on the way forward that best suits us. Certainly, I know I need those things, as well as advice from others. There have been many people who have influenced my life, but two that have made a huge difference in my career are my wife and my former business partner. Those are people I've talked with nearly every week for the last twenty years and who have influenced me more than anyone or anything else in my life. Life can be challenging at many times and finding the right path forward for ourselves can seem uncertain. A mentor can be an advisor, confidant, cheerleader, and support system. When you find someone(s) that meets those needs for you, treasure them and be sure to thank them for impacting your life. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Create an RPA Flow that Connects to Oracle Data in UiPath Studio]( JerodJ from SQLServerCentral If your company uses legacy technology systems, you can use the UiPath RPA platform to extend your process automation capabilities to these systems and eliminate repetitive, error-prone tasks of manually updating them. By using UiPath Studio, you can build an RPA program just like drawing a diagram. With the CData ODBC Driver for Oracle, users can embed Oracle data in the workflow. Let’s […] [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. [How LAG compares to other techniques]( Additional Articles from SimpleTalk LAG pulls a column from another row without a self-join. In this article, Kathi Kellenberger shows how LAG compares to other techniques From the SQL Server Central Blogs - [Slides and Video from Building a Regret-free Foundation for your Data Factory Now Available]( Meagan Longoria from Data Savvy Last week, Kerry and I delivered a webinar with tips on how to set up your Data Factory. We discussed version control, deployment, naming conventions, parameterization, documentation, and more.... From the SQL Server Central Blogs - [T-SQL Tuesday #142–What is Bicep?]( Steve Jones - SSC Editor from The Voice of the DBA It’s time for the monthly blog party, and this month the host is Frank Geisler. I was reaching out to various people last year to find hosts, and also...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The OPENJSON return schema What items do I need to include in the WITH clause of OPENJSON() to define a schema? 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) Unloading an Extended Stored Procedure Normally an extended stored procedure is not unloaded from memory until SQL Server is shutdown. How can I unload an XP without stopping SQL Server? Answer: Use DBCC FREE Explanation: You can use DBCC with the name of the DDL and then the FREE command. Ref: DBCC (dll name) FREE - [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 [SSRS 2017 How to check which SSL cert is bound]( - Hello experts, I need to update the SSL certificate on our SSRS 2017 instance. However, in the Report Services Configuration Manager it lists the 2 certs without any room to expand the name or check the properties. So happens both the old and new SSL cert that our systems team assigned start the same way, […] SQL Server 2016 - Administration [SQL Agent job]( - Can I run extended event session against 1 SQL agent job? I have a job which runs at 7 every day, takes about 10, 12, 14 hours to run. It inserts 2.5 million records into a table thru a bat file. To me, it should run fast but I am just trying to educate myself […] [Rebuild index]( - I have a table which is partitioned (Total 10). The table is 1.6 TB in size. Rebuild index on that is quite challenging, especially we have some resource constraints. I am wondering if there is a way to rebuild index, just for 1 partition. Just trying to educate myself. SQL 2012 - General [There is insufficient sys memory in resource pool 'internal' to run this query]( - Hi All, I have an issue with production server , from SQL error log I can see "There is insufficient system memory in resource pool 'internal' to run this query. when i check the memory usage for 1 month , it is like around 35% everyday and also min memory is 1 GB and max […] SQL Server 2019 - Administration [Multiple instance on SQL server 2017 with VS 2019.... ERROR 26 on Visual Studio]( - I have a system with multiple clients and each one in different virtual machines, I have proposed the task of joining all the clients in the same virtual, of course taking into account that they are all in the same version of sql server. my first tests failed as i installed each of the instances […] SQL Server 2019 - Development [Need some help with tsql code]( - I have 4 tables (DevDB, TestDB, StageDB, ProdDB). All those tables have 2 columns (DatabaseName, Environment). There are tons of DBs which exist on Prod but don't exist in lower environment. I need help with writing a query which pulls data so I can see that let's say 1 specific database exists in stage but […] [Capture login information]( - I want to Capture Login information in table or view. who connected to database and when they connected. looking for script [How to grant Exec ON multiple Stored Procedures]( - Hi, I have multiple Stored Procedures that are named like _Test_01, _Test_02 _Test_03 I would like to grant EXEC rights to multiple Stored Procedures at once. Something like: GRANT EXEC ON [dbo].[_Test*.*] TO [udr_db_FocusOneUserRole] How can I Grant these in one line? [XML data extract]( - Hi I've been tasked with extracting information for a report where the field type is XML. THe SQL table contains 2 fields Personcode and XMLBody THe XMLBody Field contains information like the below example; Extarct the data string located here. [Issue with the SUM function]( - Hello friends, I have one question. It may be a little bit hard to explain but I will try my best. Let me know if my explanation is confusing. Right now I have a query that has a chain name and lots of repetitions of the same information. I am 99,9% sure that the reason […] [Formatting issue with percent]( - I have an issue that may be an easy fix for someone, but is not for me. I need to calculate Instock%. For this I need to take p.QTY (Forecast Demand) / s.OH (What is on hand) and get a percentage. For example p.QTY (Forecast) was 2 and Inventory On Hand is 100 now so […] [SQL StoredProcedure for data validation using REGEX in SSIS]( - hi, I have a requirement where we get data from Oracle source and destination is also Oracle db. Only validation is done in SSIS. So, i have to validate the data using regular expression in SQL Storedprocedure (in ssis) and send bad data to a error log table with logging the record ( with table […] Reporting Services [Hex-tile map (e.g. United States) in SSRS?]( - I've created them in Tableau and PowerBI but would like to do this in SSRS: Powershell [SQL Server connection using Get-StoredCredential]( - Hi, I am currently able to log in using a trusted connection. I stored credentials for an SQL login in the Windows Credential Manager, but I'm not sure how I can use this for authenticating from a PowerShell script. Currently, I'm connecting: $con = New-Object System.Data.SqlClient.SqlConnection; $con.ConnectionString = "Data Source=my-apps-server;Initial Catalog=myDatabase;Integrated Security=true"; $con.Open(); # execute […] Integration Services [Save Blob Storage Key in Package]( - Hi Guys, Is there a way to save the Blob Storage Key into a package/project so that it doesn't need to be modifed after deployment each time?  cheers Alex   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -