Data won't stop aggregating

[Anonymous]

Hello all, 

I am attempting to process some data from an extended period of time across multiple sensors. What I am looking to do is to have a bar graph stacked with a line graph, but my problem is that the line graph is giving the correct values from the table, whereas the bar graph is aggregating data and summing it all up. 

I have already looked into the 'do not summarize' option, made sure it was a decimal data entry, and still no luck. I have millions of columns of data and I think the problem might be the connections I have between different queries. Below I have posted a picture of my data, and my connections. Any help would be fantastic. Thanks. 

If there's any other info needed I would be happy to provide it as this is all public information. 

[jdbuchanan71]

Hello @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.

[Anonymous]

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.

[amitchandak · Accepted solution]

Check these two solutions

https://www.biinsight.com/time-dimension-in-power-bi-and-ssas-tabular-model-supporting-minutes-time-...

https://community.powerbi.com/t5/Desktop/Help-to-insert-Hour-Half-hour-and-Minute-on-Date-Table/td-p...

[amitchandak]

I think you need to have a common time (calendar) table. It is right now taking the first date you dragged as view by.

Just swap the position of dates, the line might become flat.