Forum Discussion
Direct Query - need to see data including today's data
I am using Direct Query to show the total number of records created, but using a relative date filter of "Last 30 days, including today" ignores data that was created today, but after midnight.
Sample data:
| Date Applied | Record No |
| 7/23/19 18:45 | IMP-2019-000090 |
| 7/22/19 12:30 | IMP-2019-000089 |
| 7/18/19 21:30 | IMP-2019-000088 |
| 7/18/19 2:30 | IMP-2019-000087 |
| 7/17/19 2:30 | IMP-2019-000086 |
| 7/15/19 20:00 | IMP-2019-000085 |
| 7/10/19 12:15 | IMP-2019-000084 |
| 7/9/19 17:15 | IMP-2019-000083 |
I then created a totalizer measure
AllRecords =
CALCULATE (
COUNTROWS ( impairments_defeats ),
FILTER (
ALL(impairments_defeats),
impairments_defeats[date_applied] <= MAX ( DateTab[Date] )
)
)And here is the result I get, versus what I would expect (side by side with fact data for easy reference):
| Date | All Records | Should be | Date Applied | Record No | |
| 7/9/2019 | 0 | 1 | 7/9/19 17:15 | IMP-2019-000083 | |
| 7/10/2019 | 1 | 2 | 7/10/19 12:15 | IMP-2019-000084 | |
| 7/11/2019 | 2 | 2 | 7/15/19 20:00 | IMP-2019-000085 | |
| 7/12/2019 | 2 | 2 | 7/17/19 2:30 | IMP-2019-000086 | |
| 7/13/2019 | 2 | 2 | 7/18/19 2:30 | IMP-2019-000087 | |
| 7/14/2019 | 2 | 2 | 7/18/19 21:30 | IMP-2019-000088 | |
| 7/15/2019 | 2 | 3 | 7/22/19 12:30 | IMP-2019-000089 | |
| 7/16/2019 | 3 | 3 | 7/23/19 18:45 | IMP-2019-000090 | |
| 7/17/2019 | 3 | 4 | |||
| 7/18/2019 | 4 | 6 | |||
| 7/19/2019 | 6 | 6 | |||
| 7/20/2019 | 6 | 6 | |||
| 7/21/2019 | 6 | 6 | |||
| 7/22/2019 | 6 | 7 | |||
| 7/23/2019 | 7 | 8 | |||
| 7/24/2019 | 8 | 9 |
This is my first time using Direct Query, and I know there are limitations as to what you can do with date fields. Am I missing something simple to allow the visual to show 30 days, but "assign" the records to the correct date (DateTab is just CALENDARAUTO() ) ?
Thanks,
David
Create a column in "impairments_defeats",
DATE only = DATE(YEAR([Date Applied]),MONTH([Date Applied]),DAY([Date Applied]))
Create relationships between the "impairments_defeats" and DateTab$ based on [Date only], [Date].
Create measures in "impairments_defeats"
AllRecords = CALCULATE ( COUNTROWS ('impairments_defeats$' ), FILTER ( ALL('impairments_defeats$'), 'impairments_defeats$'[DATE only] <= MAX ('DateTab$'[Date]) ) )To meet your needs, i create a modified measure
modify all recoreds = var AllRecords = CALCULATE ( COUNTROWS ('impairments_defeats$' ), FILTER ( ALL('impairments_defeats$'), 'impairments_defeats$'[DATE only] <= MAX ('DateTab$'[Date]) ) ) return IF(MAX('DateTab$'[Date])=[Today],AllRecords+1,AllRecords)Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly."Create a column in "impairments_defeats",
DATE only = DATE(YEAR([Date Applied]),MONTH([Date Applied]),DAY([Date Applied]))
Create measures in "impairments_defeats"
AllRecords = CALCULATE ( COUNTROWS ('impairments_defeats$' ), FILTER ( ALL('impairments_defeats$'), 'impairments_defeats$'[DATE only] <= MAX ('DateTab$'[Date]) ) )"
I used the above 2 parts of the solution provided. When creating a relationship between DATE only and Date, there are zeros on the dates where there is no record in impairments_defeats, and the count is not cumulative:
I disabled the relationship and the results came as expected. I also do not understand the purpose of "modify all records". Why would I want to add 1 to the count on the current day, if there is not a record that was applied on that day? The value for AllRecords is now correct based on the use of the "DATE only" column.
Thank you for your assistance,
David
2 Replies
Create a column in "impairments_defeats",
DATE only = DATE(YEAR([Date Applied]),MONTH([Date Applied]),DAY([Date Applied]))
Create relationships between the "impairments_defeats" and DateTab$ based on [Date only], [Date].
Create measures in "impairments_defeats"
AllRecords = CALCULATE ( COUNTROWS ('impairments_defeats$' ), FILTER ( ALL('impairments_defeats$'), 'impairments_defeats$'[DATE only] <= MAX ('DateTab$'[Date]) ) )To meet your needs, i create a modified measure
modify all recoreds = var AllRecords = CALCULATE ( COUNTROWS ('impairments_defeats$' ), FILTER ( ALL('impairments_defeats$'), 'impairments_defeats$'[DATE only] <= MAX ('DateTab$'[Date]) ) ) return IF(MAX('DateTab$'[Date])=[Today],AllRecords+1,AllRecords)Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly."Create a column in "impairments_defeats",
DATE only = DATE(YEAR([Date Applied]),MONTH([Date Applied]),DAY([Date Applied]))
Create measures in "impairments_defeats"
AllRecords = CALCULATE ( COUNTROWS ('impairments_defeats$' ), FILTER ( ALL('impairments_defeats$'), 'impairments_defeats$'[DATE only] <= MAX ('DateTab$'[Date]) ) )"
I used the above 2 parts of the solution provided. When creating a relationship between DATE only and Date, there are zeros on the dates where there is no record in impairments_defeats, and the count is not cumulative:
I disabled the relationship and the results came as expected. I also do not understand the purpose of "modify all records". Why would I want to add 1 to the count on the current day, if there is not a record that was applied on that day? The value for AllRecords is now correct based on the use of the "DATE only" column.
Thank you for your assistance,
David