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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!