Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

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

DaHolla_0-1627398938542.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.