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
Super 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!

    • danextian's avatar
      danextian
      Super User
      1 year ago

      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.

      • glearner's avatar
        glearner
        Frequent Visitor
        1 year ago

        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 except DeliveryMetrics.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 support