Dual Mode Inconsistency

[DaHolla]

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.

[v-janeyg-msft]

Hi, @DaHolla 

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

Best Regards,

Community Support Team _ Janey

[DaHolla]

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]

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

[DaHolla]

@v-janeyg-msft 
That appears to be how my tables are setup:

[v-janeyg-msft]

Hi, @DaHolla 

I test it and it's different with you. Is your DQ data source from powerbi datasets? When you write measure, the fields in the date table should not appear automatically,right? 

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

[DaHolla]

Hi @v-janeyg-msft,

The fields do appear automatically from the date table, with both the Sales_Agg and Sales_Detail tables. Another reason why I'm so confused the measures behave differently. 

[lbendlin]

Please show the relevant part of your data model.

[DaHolla]

@lbendlin 
Scrubbed, but here are the 3 tables involved: