cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaHolla
Frequent Visitor

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.

8 REPLIES 8
v-janeyg-msft
Community Support
Community Support

Hi, @DaHolla 

 

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

 

Best Regards,

Community Support Team _ Janey

 

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.

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.

vjaneygmsft_0-1627350983625.png

vjaneygmsft_1-1627351017768.png

 

vjaneygmsft_2-1627351235892.png

 

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

 

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

DaHolla_1-1627398984569.png

 

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? 

vjaneygmsft_1-1627455164458.png

 

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

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

Please show the relevant part of your data model.

DaHolla
Frequent Visitor

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

DaHolla_0-1627398938542.png

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!