Forum Discussion
Average by group
Hi glearner
The measure below will remove any filters coming from the Dates/Calendar table but will respect the filter from other tables.
// Calculates the total online hours across all dates, ignoring any filters on the Dates table. CALCULATE([Online Hours Measure], ALL(Dates))For this measure to work, you must be using a separate Dates dimension table with a one-to-many single direction relationship to your fact table.
Hi glearner
The measure below will remove any filters coming from the Dates/Calendar table but will respect the filter from other tables.
// Calculates the total online hours across all dates, ignoring any filters on the Dates table.
CALCULATE([Online Hours Measure], ALL(Dates))
For this measure to work, you must be using a separate Dates dimension table with a one-to-many single direction relationship to your fact table.
Hi danextian , I feel dumb
Using the All Date provides the intended output, however I was using All('Date'[DateKey]) which didn't.
Do you have any idea why ? Confirming the relationship with the date table is a 1 to *, based on datekey.
see below for a screenshot
Thanks!
Using ALL('Date'[DateKey]), the filter modifier is applied exclusively to the DateKey column of the Date table. This means that filters specifically targeting the DateKey column are removed, while filters from other columns within the Date table or from any related tables will still remain active and influence the calculation. So if you use the actual Date column instead of the DateKey column, filters will still apply.
danextian So you are suggesting that somewhere, somehow, the semantic model is filtering the fact table based on a field inside teh calendar table that is not datekey? Gosh I hate this model...
I was hoping to calculate this averages by store and delivery partner.
Would an all exceptDeliveryMetrics.StoreKey & DeliveryMetrics.PartnerKey be fit for purpose?Is there a way to limit the All based on Date Selected in the Slicers? AllSelected(Date) perhaps?
Thansk for your ongoing supportThere is a key difference between modifying the filter context on a column versus on a table. Using ALL('Table'[Column]) removes the filter specifically from that column. As long as that column is referenced, the filter is ignored. For example, if you include DateKey in a visual along with a measure that applies ALL to it, the same value will appear across all rows of the column, even if a slicer is applied to DateKey . In this case, only the visible rows will change based on the slicer selection, but the measure's values will remain consistent across the entire column due to the ALL function.
On the other hand, if you filter by actual dates using a slicer, the values will adjust to reflect the filtered range. However, the measure will still display the same value across the entire DateKey column because the ALL function was applied to that column specifically.
This highlights why it is considered best practice to use a separate date table. In denormalized tables where all data resides in a single table, applying ALL to the entire table will prevent the values from changing, regardless of the filters applied to it.
It is a practice for some to use DateKey in a relationship instead of the actual dates because that what it is in the source database.
Is there a way to limit the All based on Date Selected in the Slicers? AllSelected(Date) perhaps?Yes, you can use ALLSELECTED to limit the scope of ALL based on the current selection in slicers or visuals. However, it’s important to understand how ALLSELECTED behaves. It respects the filters applied in the current context but removes lower-level filters within the hierarchy.
For example, if you add columns such as Date, Month, Quarter, and Year from a date table into a visual, and your measure uses ALLSELECTED, the values will remain consistent across all rows within the currently selected context. If you select Q1 in the slicer, the measure will show the total value for Q1 in all cells, regardless of the individual dates, months, or other lower-level filters.
This happens because ALLSELECTED retains the filters applied at higher levels (like Q1) while ignoring any finer-grained filters (like individual dates). It's useful when you need the measure to respect the slicer selection but aggregate at a broader level within that selection. For example, in cases when you want to compare the individual days against the total of all visible days.