Forum Discussion

DaHolla's avatar
DaHolla
Frequent Visitor
5 years ago

Dual Mode Inconsistency

I am writing a measure that uses a sales table (direct query) and our date (dual) table. The sales table has some aggregate (import) tables as well. The measures uses SUMMARIZE, and when I write the measure (Summarize Test, below) using the aggregate tables, it works as expected. However, when the same measure (Summarize Test 2, below) uses the detail table, I get the "The column 'DayOfWeek' specified in the 'SUMMARIZE' function was not found in the input table" error:

 

Summarize Test =
COUNTROWS (
     SUMMARIZE (
          Sales_AGG,
          Sales_AGG[Date],
          Sales_AGG[Store],
          'DATE'[DayOfWeek]
     )
)

Summarize Test 2 =
     COUNTROWS (
          SUMMARIZE (
          Sales_Detail,
          Sales_Detail[Date],
          Sales_Detail[Store],
          'DATE'[DayOfWeek]
     )
)


Our date table is a Dual Storage Mode table, so I would expect the measure to work the same as with the aggregate tables. Any help would be appreciated.

Need Help

8 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, DaHolla 

     

    Is there a relationship between 'Sales_Detail' and 'DATE'? We need more information to judge.

     

    Best Regards,

    Community Support Team _ Janey

     

    • DaHolla's avatar
      DaHolla
      Frequent Visitor
      5 years ago

      Sorry, I thought I provided the join info in my post!
      Sales_Detail is joined to DATE on a datekey, many to one cardinality, single cross filter direction, relationship is active. The join to Sales_Agg is identical.

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        5 years ago

        Hi, DaHolla 

         

        The direction of the relationship is very important. Generally speaking, the relationship between the calendar table and the main table should be one-to-many, not many-to-one.

         

         

        Did I answer your question ? Please mark my reply as solution. Thank you very much.
        If not, please feel free to ask me.


        Best Regards,

        Community Support Team _ Janey