How-to-Dax-Power-BI-Tutorial-Distinct-Count

Distinct Count and Filters – How many Open Cases do I Have Today?

Being a Power BI enthusiast, I always find DAX a challenge but have found success by keeping to 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 even debugging Power BI DAX is to take things step-by-step. You can always get more complicated later on. The source files are available here; PowerBI_Dax_Tutorial (github.com)

Case Details
- Metric Details
- Data Source
Key to Power BI DAX: Take it step-by-step
Conclusion
Resources & Source Files

Case Details

The case owner has a Power BI Dashboard that reports on procedures that are currently being delivered. There is a waiting list application where the procedures are listed until they become active. A case may be put back on the list with the same case number but will never be active in both records.

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 imported into Power BI from and 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, which was created as a filter for selecting. When we talk about the date we are looking at for analysis, we will call it “Today” or “Selected Date.”

Sample Power BI Report with Date Selection

Key to Power BI DAX: Take it step-by-step

What we want to do is create a calculated measure that calculates over and above the current filter context. We want to select one day, but want the value to roll up over a number of days and based on 2 columns.

We want to create a recordset (Table in Dax terms) with all of the waitlist items that are open, 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 is really evaluating the expression given by the user with all the applied filters.

First step: Write the calculation in words or Pseudo Code

I find that writing the requirements for the calculation in a sentence and breaking it down into parts helps define each step that I want 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

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.

Add a Measure to the Procedure Wait List Table

Count Distinct (Unique) Cases = DISTINCTCOUNT(‘Procedure Wait List'[CaseID])

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. We want to filter on that distinct count that matches our metric we want to create as some of these cases were removed.

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.

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 that there are 9 Projects on the Wait List.

Date Flter = CALCULATE(DISTINCTCOUNT(‘Procedure Wait List'[CaseID]),
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 not a date, or if 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;

or(‘Procedure Wait List'[RemovedfromWaitListDate]>= MAX(CalendarTable[Date]), ISBLANK(‘Procedure Wait List'[RemovedfromWaitListDate]))

This gives us the full formula, which includes the && – And with the OR function.

Active Waitlist Procedures = CALCULATE(DISTINCTCOUNT(‘Procedure Wait List'[CaseID]),
FILTER(‘Procedure Wait List’,
‘Procedure Wait List'[OnWaitListDate] <= MAX(CalendarTable[Date]) && or(‘Procedure Wait List'[RemovedfromWaitListDate]>= MAX(CalendarTable[Date]), ISBLANK(‘Procedure Wait List'[RemovedfromWaitListDate]))
))

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. Not only development testing but end-user testing by the business users. The data owners will know more possibilities than is apparent during development. (Just some selected cases, not all.)