Understanding a DAX formula

[CPIBecklon]

Please consider the following formula: 

Sample% = DIVIDE(COUNT(DateTimeCompleted[ID]), CALCULATE(COUNT(DateTimeCompleted[ID]),ALLSELECTED(DateTimeCompleted)))

So...I guess I don't understand how this is calculating correctly.

To me, the numerator is a count of ALL IDs in the DateTimeCompleted table. That could be a big number.

The denominator, on the other hand, says count the same thing, but take into account the outside filters. Right? Thus, it seems to me that this number would be smaller.

Am I not thinking correctly?

By the way, the outside filters are the following: (1) One field that identifies outliers and  (YES or NO) and (2) One field that identifies an order that was added a while after the initial order (YES or NO). Both of these are built into the DateTime completed table.

Any help that can be provided in understanding this formula will be greatly appreciated.

Thank you!

[AmiraBedh]

The numerator COUNT(DateTimeCompleted[ID]) counts the number of ID in the current filter context.
If you have any filters applied outside (for example in a visual or slicers), it will respect that.
For instance, if you've filtered on a specific date range or other criteria, only the IDs within that filter context will be counted.

For the denominator CALCULATE(COUNT(DateTimeCompleted[ID]),ALLSELECTED(DateTimeCompleted))

Here, CALCULATE modifies the filter context. ALLSELECTED(DateTimeCompleted) removes all filters from the DateTimeCompleted table, but retains any filters that have been directly applied by the user.

If a user selected 'YES' for outliers using a slicer, the numerator will count only those IDs with 'YES' for outliers. The denominator will count all IDs regardless of their outlier status but will retain the filter for other selections like if a certain order was added a while after the initial order.

Your understanding that the denominator should generally be larger is correct because it's looking at a broader dataset, while the numerator is more specific due to the filters.

[CPIBecklon]

Amira...for the most part I understand. There is one more column that should have been mentioned. Let's call it "Item". The two possibilities for Item are "Candy" or "Cake". If I filtered "Cake", then the denominator should remove that filter and include Candy and Cake....right? Yet, it appears that the denominator is only counting Cake...which I want.

Maybe I should also say that the "ID" and the "Item" columns were directly built into Power Query, where as the Outlier column and the Addon column are calculated columns. Will this have any impact?

Thank you for your patience!

[AmiraBedh · Accepted solution]

When you use ALLSELECTED(DateTimeCompleted), it should indeed remove all the filters from the DateTimeCompleted table, but still respect any filters directly applied by the user. So, if you select "Cake" through a slicer or visual,
the denominator should technically consider both "Cake" and "Candy", since ALLSELECTED would respect the broader context of the table but would retain the direct user selections.

Whether a column is directly built into Power Query or if it's a calculated column typically does not affect how ALLSELECTED works. Both types of columns are part of the data model once they are loaded into PBI, and DAX treats them the same way.

However, the specific way the calculated columns are defined might introduce unexpected filter context.
But, given the formula you've shared, this doesn't seem to be directly influencing the issue you described.

What I suggest if you want the denominator to explicitly count only "Cake", regardless of your selections, you can modify your code like below :

Sample% = DIVIDE(
COUNT(DateTimeCompleted[ID]),
CALCULATE(
COUNT(DateTimeCompleted[ID]),
ALL(DateTimeCompleted),
DateTimeCompleted[Item] = "Cake"
)
)

In this version, ALL(DateTimeCompleted) removes all filters from the DateTimeCompleted table.
After that,you're explicitly setting the filter context to only consider rows where Item is "Cake".

[CPIBecklon]

Amira: The formula does, in fact, work as I wanted it to work. However, I wanted to ensure that I understood the mechanism that drives the result. I am so very grateful for your help with this! Thank you!

[AmiraBedh]

Glad to help 🙂