Forum Discussion
Data won't stop aggregating
Super UserHello Anonymous
In order to show the Flow Data and Rain Data compared by dates you are going to need a date table sitting over both your fact tables. It looks like you are displaying by day so would you be able to add a column to both Flow and Rain that has just the date rather than the date/time? Then your date table would only need the date.
This code will get you a basic date table based on the dates in your model.
Dates =
VAR DateRange = CALENDARAUTO()
RETURN
ADDCOLUMNS(
DateRange,
"Year",YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"MonthNum", MONTH ( [Date] ),
"Month Year", FORMAT ( [Date], "mmm-yyyy"),
"MonthYearNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
"Quarter Year", "Q" & FORMAT ( [Date], "q-yyyy" ),
"QtrYearNum", YEAR ( [Date] ) * 100 + VALUE ( FORMAT ( [Date], "q" ) )
)
Then, in your graphs you pull the date from the date table which would filter down to both Flow and Rain, and the amounts should display correctly.
Hi there, thanks for the response. I understand my problem with having two date tables for my data, however, I do need the DateTime table to be in terms of minutes as the flow data changes by the minute and we would need to determine exactly what time problems occur.
I had tried to make a date table including minutes before but I have not been successful in writing the script. Do you have any suggested tips for this?
Thanks again
- jdbuchanan71
I've not done any work myself with data at the minute granularity but I have read that the way to handle it is to split it into 2 fields, one for date and one for time and to keep the precision as low as is useful (if you don't need to go to the seconds level then don't). That way you have a year of dates which is 365 rows and a table of hh/mm which is 1440 rows rather than 31.5 M rows for Date/hh:mm:ss for a single year.
Then you link Flow and Rain into both the Date and Time tables.
You can make a time table in excel just by starting with 1/24/60 in a cell (this is 1 minute in excel terms) then add that to 0:00 and fill down. Your list will increment by 1 minute on each row.