How to DAX: Distinct Count and Filters
As a Power BI enthusiast, I always find DAX challenging but have found success by following a couple of simple rules. In this Power BI Tutorial, we review my DAX development workflow through an example case, where we will use Power BI Desktop to create a KPI measure to see how many medical cases are on a waitlist on any given date.
The key to learning or debugging Power BI DAX is taking things step-by-step. You can always get more complicated later on. The source files are available here: PowerBI_Dax_Tutorial (github.com)
Distinct Count and Filters – How many Open Cases do I Have Today?
Case Details
The case owner has a Power BI dashboard that displays reports on procedures currently being delivered. Procedures are stored in a waiting list application until they become active. A case may be returned to the list under the same case number, but it will never be active in both records simultaneously.
Metric Details
The metric is the count of procedures on the waitlist on any given date. The number of cases does not roll up and is always at a point in time.
Data Source
The sample data was imported into Power BI from an Excel file. The source below has three fields.
CaseID – The ID could be duplicated, so any counts need to be distinct.
TableID – The record number will make it easy to see the number of records for validation.
OnWaitListData – This is the date that the case went onto the waitlist.
RemovedFromWaitList – The date that the case was removed from the waitlist and entered the next stage. If the date is empty, the case has not been removed and counted in the total.
We also have a Date table, created as a filter for selecting. When we discuss the date we are looking at for analysis, we will call it “Today” or “Selected Date.”
Sample Waiting List Data
Sample Power BI Report with Date Selection
Key to Power BI DAX: Take it step-by-step
We want to create a calculated measure that calculates over and above the current filter context. We want to select one day, but we want the value to roll up over a number of days and be based on two columns.
We want to create a record set (Table in Dax terms) with all of the open waitlist items, no matter when they were added or when they might be removed. The Dax Calculate function definition is “Calculate Evaluates an expression in a modified filter context,” which evaluates the expression given by the user with all the applied filters.
First step: Write the calculation in words or Pseudo Code
Writing the requirements for the calculation in a sentence and breaking it down into parts helps define each step I want to look at when developing the DAX measure.
Number of Open Cases on the waitlist =
# Cases where the On List Date is <= Date Selected and (the Removed from list Date is >= Date Selected or the Removed Date is blank)
Remember the Key !!!! The key to DAX success is to take things step-by-step. You can always get more complicated later on. So, let’s take the table and count how many cases are on the waitlist.
Second Step: Take a portion of the calculation first
Add a New Measure to the table.
Let’s get a Distinct Count of the number of cases in the table. This is a nice bite-sized portion. There are 11 records, but Case #2 was placed back on the list on Dec 12, so there are only 10 unique cases.
Remember that a case can only have one active record on the list, and Case #2 was removed on Feb 2.
We added the calculation to the table pictured above and then put that measure on a Power BI Card visualization. You can see here that we have a Distinct Count of the Case IDs. As some of these cases were removed, we want to filter on that distinct count to match the metric we want to create.
Metric – Count of the Unique Case IDs
Third Step: Let’s add a filter to the Distinct Count.
We now want to get a distinct count of the cases on the waitlist where the Waitlist Data is less than or equal to the date that is selected for the report. Pictured below is a Data Table with Oct 11, 2021, selected. When we add in the filter, we should get 10 as an answer.
Distinct Count shows 10 unique records
We added a FILTER to the Distinct Count calculation. We now see all the Procedures on the waitlist that are less than or equal to the date selected. Here, we are using the Max(CalendarTable[Date} value, which is the filter on the report. Selecting the Date Filter changes the calculation values. When we select October 12, we see 9 Projects on the Wait List.
FILTER(‘Procedure Wait List’, ‘Procedure Wait List'[OnWaitListDate] <= MAX(CalendarTable[Date])))
Fourth Step: Add the “Removed From Waitlist Date” Logic
Two conditions make the Removed From Waitlist important. If the Case was removed, then it should not be in the count. If there is no date or the cell is blank, we need to count the case as open. We will use the Dax IFBLANK function, which checks whether a value is blank and returns TRUE or FALSE.
To the date filter, we are going to add this logic together into a DAX OR function. Whether one is true, this Case will be included in the count;
This gives us the full formula, which includes the && – And with the OR function.
Remember our starting point;
Number of Open Cases on the waitlist =# Cases where the On Date is <= Date Selected and (the Removed Date is >= Date Selected or the Removed Date is blank)
Or a picture of the final calculation might be better.
Final step: Let’s look at a couple of test cases.
As with any development, it is essential to test, test, test, and get your use cases before development starts. This includes not only development testing but also end-user testing by the business users. The data owners will know more possibilities than are apparent during development. (These are just some selected cases, not all.)
Test Case 1 – January 1st – Testing the date equal.
Test Case – As of Jan 2, 2021 – All 8 are valid.
Test Case – As of Oct 11, 2021 – Shows Dec 1 Roll of is counted, and others roll off.
Test Case: As of Dec 15, 2021 – Shows the Case #2 coming back on and only counts once.
Conclusion
The best thing you can do when working with DAX is to take it step-by-step. There are many resource available. The Best book I have found is Definitive Guide to DAX, The: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd Edition | Microsoft Press Store.
Resources & Source Files
Source Files: 5MinuteBIRepo/PowerBI_Dax_Tutorial at master · steveyoungca/5MinuteBIRepo (github.com) – This is the Excel data source and Power BI file used in this walkthrough.
Solved: DISTINCTCOUNT with condition – Microsoft Power BI Community – Question and answer about using filters with DistinctCount
Free MS Learn Module 3 hrs: Introduction to creating measures using DAX in Power BI – Learn | Microsoft Docs
DAX overview – DAX | Microsoft Docs – Overview and header to the DAX documentation.
