Forum Discussion
Helper IUnderstanding a DAX formula
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".
Helper IAmira...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!
Super UserWhen 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 🙂