Stairway to DAX and Power BI - Level 12: The DAX CountA() and CountAX() Functions (2021-08-25)

SQLServerCentral Newsletter for August 25, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to DAX and Power BI - Level 12: Function / Iterator Function Pairs: The DAX CountA() and CountAX() Functions]( - [Always On Availability Groups Troubleshooting and Monitoring Guide]( - [Azure Data Factory Incremental Pipeline from Azure SQL Database to Databricks]( - [From the SQL Server Central Blogs - Divide the rows in equals batches]( - [From the SQL Server Central Blogs - SSMS and SQLCMD: Prevent T-SQL Batch From Not Only Executing, but Also From Parsing (Cruel Joke #3)]( Question of the Day - [Python Mapping]( The Voice of the DBA  Daily Coping Tip Be thankful for your food and those who made it possible 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. Does Log Data Belong in a Table or a File? Today we have a guest editorial from Andy Warren as Steve is on vacation. This was originally published on 6 Sept 2016. I worked on a project recently where we wanted to consolidate the errors from an application error logging table stored across multiple databases to a monitoring repository (not SQL Server) for analysis and dashboarding.  As we talked about options for accomplishing this, another member of the team remarked that all log data should go in files, period. My reply was that I thought it was far from that simple (and made a note to write this to get your opinions!). Writing to a file is fast, no doubt about it, and has lower overhead than logged transactions to a table. Files in well known formats, such as syslog and the Windows Event Log, are ingested directly by common log aggregation and correlation tools. As DBA's we don't have to rebuild indexes on those files or run jobs to delete rows older than x days or x years that may require chunking or partitions to work effectively or worry about run away error conditions that can log millions of rows in a short time. That adds up to a pretty good case for using files. Is there a case for using a table? One big advantage of a table is usability. Querying a table is easy, far easier in most cases than querying a file directly. It's also typically faster because we can add indexes to support the common queries. Tables allow a log design to have situation specific columns compared to the more generic log formats. Tables also allow us to easily layer on a security model so that we can let certain users see certain kinds of rows. Some kinds of log data such as multi-tenant application audit logs are so critical that they must be stored in the database so that they get the same level of HA and DR coverage as the 'real' data. For me the main consideration is how the data will be used. If it's just going to be used for security analysis or the occasional debug scenario I'm far more apt to recommend a file based approach if the volume is high. If it's low, who cares, write it to a table. If the data is going to be used by users frequently, then it has to be in a table, or a system that support SQL'ish queries with fast response times and the ability to apply security to what they can retrieve. After writing this and thinking about it I still don't think it's as simple as "all logs should be in files". Do you agree? Have different concerns when making the decision? I'm looking forward to the discussion. Andy Warren [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to DAX and Power BI]( [Stairway to DAX and Power BI - Level 12: Function / Iterator Function Pairs: The DAX CountA() and CountAX() Functions]( Bill Pearson from SQLServerCentral.com Business Intelligence Architect, Analysis Services Maestro, eight-year Microsoft Data Platform MVP and author Bill Pearson introduces the DAX CountA() and CountAX() functions, discussing the syntax, uses and operation of each. He then provides hands-on exposure to CountA() and CountAX(), in counting non-empty cells in a column, and in counting nonblank results when evaluating the result of an expression over a table, respectively. [Always On Availability Groups Troubleshooting and Monitoring Guide]( Steve Jones - SSC Editor from SQLServerCentral This guide helps you get started on troubleshooting some of the common issues in AlwaysOn Availability Groups and monitoring AlwaysOn Availability Groups. It is intended to provide original content as well as a landing page of useful information that is already published elsewhere. [Azure Data Factory Incremental Pipeline from Azure SQL Database to Databricks]( Additional Articles from MSSQLTips.com In this tip find out how to use Azure Data factory to build an incremental pipeline from Azure SQL Database to Databricks. From the SQL Server Central Blogs - [Divide the rows in equals batches]( Brahmanand Shukla from SQL Server Carpenter Here is the simple and practical use case of NTILE function. We’ve used it to divide the rows of sys.columns into N batches. N is the batch size. We’ve... From the SQL Server Central Blogs - [SSMS and SQLCMD: Prevent T-SQL Batch From Not Only Executing, but Also From Parsing (Cruel Joke #3)]( Solomon Rutzky from SQL Quantum Leap (last updated: 2021-08-13 @ 19:50 ET / 2021-08-13 @ 23:50 UTC ) In a previous post, Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC), I explained how...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Python Mapping I have this code: def square(i): return i * i a = a = (1,2,3,4) x = map(square,a) print(list(x)) What is returned by this code? 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) Starting FTS Population I have created a Full-text Search index, but used manual population. How can I start the population of the index? Answer: Use the ALTER FULLTEXT INDEX command with the START FULL POPULATION Explanation: The ALTER FULLTEXT INDEX command has a START FULL POPULATION option. Ref: ALTER FULLTEXT INDEX - [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 2016 - Administration [Suppress Backup Messages In Logs]( - I'm trying to get rid of all backup messages in my SQL logs and I was able to successfully suppress completed notifications by enabling trace flag 3266. However, I'm still dealing with "backup passed" and "backup skipped" entries which quickly clutters the log when performing log backups every 10 minutes on dozens of databases. The […] SQL Server 2016 - Development and T-SQL [Gap in dates query]( - Hello All, I need your help in building this query. create table #memb (ID int, eff_dt date, term_dt date, prod varchar(5)); insert into #memb select 1111 , '01/01/2016' , '12/31/2016', 'AAA' union all select 1111, '01/01/2017', '12/31/2017', 'AAA' union all select 1111, '01/01/2018', '03/31/2021', 'AAA' union all select 1112, '01/01/2017', '12/31/2017', 'AAA' union all select […] Development - SQL Server 2014 [Incorrect Syntax near End]( - Hi if Month(@frDate) < 4 begin SET @ytdfrDate = (SELECT CONVERT(DATE, dateadd(y,-1,@frdate + '-04-01')) end Thanks [YTD Date calculation]( - Hi I have @frdate parameter. In below condition i want to Set date as '01/04' and subtract 1 year from @frdate if Month(@frDate) < 4 begin SET @ytdfrDate = "01/04/" + dateadd(y,-1,@frdate) end Thanks  [YTD Sale]( - Hi I have below Stored Procedure . I want YTd column value also. If user enters @frDate = 01/01/2021 and @toDate = 31/01/2021 then YTD value should be from 01/04/2020 to 31/01/2021 If user enters @frDate = 01/12/2020 and @toDate = 31/12/2020 then YTD value should be from 01/04/2020 to 31/12/2020 Create PROCEDURE [dbo].[SpBpGroupWiseSalePurchase] @frDate […] SQL 2012 - General [Function for converting hh:mm:ss time to decimal equivalent]( - I have a user defined function that just needs a little tweaking. The first block of code used in a query returns 7 decimal places and when I wrap the code in a function it only returns 2 decimal places. Any clue would help... Thanks! ,CONVERT(DECIMAL(10,2),LEFT([LOGIN TIME],2) ) + CONVERT(DECIMAL(10,2),SUBSTRING(CONVERT(VARCHAR(8),[LOGIN TIME]),4,2))/60 + CONVERT(DECIMAL(10,2),RIGHT([LOGIN TIME],2))/3600 AS […] SQL Server 2012 - T-SQL [How to get the detailed record and summary value at the same time]( - there are LineNo, itemNo and qty fields in one table, I want to the accumulative qty (totalQty) for each item and want to the totalqty shown as below picture, I just know one way to get the the result , could you please help me how many ways to get the result, it seems that […] SQL Server 2019 - Administration [Complete permissions script]( - Hi Everyone Is there a script that can output a FULL audit of my SQL instance. As in script out: Logins Server roles Server securables Database Users Database Roles Database securables  I have bits and pieces of this but cannot find a complete solution.  Any help is appreciated  Thanks [Is that a reason DBA should Install SQL Server]( - Hello: I got a question from upper manager and he wanted to know why DBA should install SQL Server? Should Window Server Team install SQL Server? I tried to explain and clarify to him. Would you please give me a good reason why DBA should install SQL Server?  Thank you in advance. Edwin [Returning Database Properties related to "Change Tracking"]( - The SSMS Database Properties screen has a page "Change Tracking". How can I get those properties via regular SQL? The normal way to return database properties is to use sys.databases or DATABASEPROPERTYEX, but I could not find those properties via either approach. The SQL instance is 2019 - Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - […] SQL Server 2019 - Development [T-SQL Unique Index - classification of unique - advice needed]( - I have inherited a data warehouse with Type II SCD tables. Each row has an entity_identifier (not null), StartDate (not null), EndDate (nullable) and then the entity attributes. When an attribute changes, the EndDate is completed and a new record added with the new StartDate. So far - So good Each table also has a […] SQL Server 2008 - General [Service Broker limit in SQL]( - As mentioned in the link below, For client-side applications, query notification users should not exceed ten concurrent users , in SQL Server 2005. What is the maximum number of concurrent users allowed while using query notification in SQL 2008 and above? General Cloud Computing Questions [Questions about Cloud Computing]( - I am currently a second year Information Technology student and I aspire to be a Data Analyst or be a DBA and I have currently learned SQL through MySQL Workbench and have created personal projects and I was just wondering as it pertains to cloud computing (Azure SQL or AWS SQL) is it best I […] SSRS 2012 [SSRS 2012 Email Relay question]( - Hi All, I am trying to figure out if it is possible to send SSRS subscriptions via email directly through our office 365 environment, and I am having issues. I can't seem to find documentation of if this is possible in SSRS 2012 (I know they added it in 2016)., and what settings I need […] Analysis Services [SSAS Referenced Dimension]( - Ahoi, i have been looking at the different relationship options the Multidimensional Model offers, because i only have the Regular and the M:N ones in actual use. The one that confuses me is the Referenced Dimension type. From my understand it allows connecting a Dimension and Facts by using an intermediate Dimension which has a […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -