Why do some entries disappear when I connect to a date table?

[Gerbil]

Here's a screenshot of two tables. One has Authorized Date, Total Estimates, and ID from an estimates table, and the values from the Date table. The other one just solely has values from the estimates tables.

As you can see, in the table with values just from the estimates table, it displays both A and B. But when you add a column from the date table, value B disappears.

Here's the relationship:

There is a relationship between Date and Authorized Date that is single direction filter, many to one.

So why does entry B disappear when I add Date from the Date table?

[johnt75]

Make sure that April 8 exists in the date table.

[Gerbil]

It exists, it has every date from 1/1/2000 to current

[johnt75]

This may not do anything but try moving the Total Estimates measure after the columns from the date table.

If that doesn't change anything, can you use Performance Analyzer to grab the queries for both the visuals and post those, along with the definition of Total Estimates.

[Gerbil]

Total Estimates is just a value pulled from a table; no transformations or modifications.

Here's what I got from the Performance Analyzer:

// DAX Query

DEFINE

VAR __DS0FilterTable = 

TREATAS({"A",

"B"}, 'ESTIMATES'[ID])

VAR __DS0Core = 

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL(

ROLLUPGROUP(

'ESTIMATES'[Authorized Date],

'ESTIMATES'[ID],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Year],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Quarter],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[QuarterNo],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Month],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[MonthNo],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Day]

), "IsGrandTotalRowTotal"

),

__DS0FilterTable,

__DS0FilterTable2,

"Total_Estimates", 'Measures (2)'[Total Estimates]

)

VAR __DS0PrimaryWindowed = 

TOPN(

502,

__DS0Core,

[IsGrandTotalRowTotal],

0,

'ESTIMATES'[Authorized Date],

1,

'ESTIMATES'[ID],

1,

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Year],

1,

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[QuarterNo],

1,

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Quarter],

1,

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[MonthNo],

1,

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Month],

1,

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Day],

1

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

[IsGrandTotalRowTotal] DESC,

'ESTIMATES'[Authorized Date],

'ESTIMATES'[ID],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Year],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[QuarterNo],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Quarter],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[MonthNo],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Month],

'LocalDateTable_5e35c2db-bc73-4008-9b35-5fb09e953ded'[Day]

// Direct Query

SELECT

 SUM ( "t0"."Total Estimate" )

 "a0"

FROM 

(

(

----------ESTIMATES----------

SELECT

to_date(auth_date) as "Authorized Date"

,NUM as "ID"

,TOT_EST as "Total Estimate"

FROM estimates

 "t0"

// Direct Query

SELECT

"t0"."ID","t0"."Authorized Date"

FROM 

(

(

----------ESTIMATES----------

SELECT

to_date(auth_date) as "Authorized Date"

,NUM as "ID"

,TOT_EST as "Total Estimate"

FROM estimates

)

)

// Direct Query

SELECT

 SUM ( "t0"."Total Estimate" )

 "a0"

FROM 

(

(

----------ESTIMATES----------

SELECT

to_date(auth_date) as "Authorized Date"

,NUM as "ID"

,TOT_EST as "Total Estimate"

FROM estimates

)

 "t0"

[johnt75 · Accepted solution]

Its interesting that despite having a date table the query is still pulling from an automatically defined local date table.

Try marking your date table as a date table, see if that has any effect.

[Gerbil]

That worked! Now I have to fix the relationship so that the date shows up for both entries. But the table is displaying all the entries. Thank you for your help!