Forum Discussion

glearner's avatar
glearner
Frequent Visitor
1 year ago
Solved

Average by group

Hi    I have a fact table that contains online hours by day, store, and partner platform. The fact tables has a 1:* relationship coming from Dims Storekey,PartnerKey, and DateKey   How can I cal...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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.

glearner's avatar
glearner
Frequent Visitor
1 year ago

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!