Forum Discussion

Regular Visitor

4 years ago

Split a source table (x,y) into two tables based on date and plot both in the same scatter plot

Hi all, I have a time series and for each day there is the (x) max air temperature logged and the (y) power consumption of a theoretical device for this respective day. I am trying to crea...

Icon for Super User rank

Super User

4 years ago

Mike_ , Based on what I got. keep both slicer on independent date tables and filter the joined date table

//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

//Date2 is independent Date table
new measure =
var _max = maxx(allselected(Date2),Date2[Date])
var _min = minx(allselected(Date2),Date2[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Mike_
Regular Visitor
4 years ago
amitchandak Thank you for your reply.

So, you mean I should create another Date table/column (i.e. 'Date'[Date]) and two measures as per below, per axis and then insert them in one scatter plot ?

If understood well, the following visual is created:

Aggregation

//
Measure 1(y) =
var _max = maxx(ALLSELECTED('Table'[Date]), 'Table'[Date])
var _min = minx(ALLSELECTED('Table'[Date]), 'Table'[Date])
return
CALCULATE(
sum('Table'[Energy Consumption (kWh)]), FILTER('Date', 'Date'[Date] >= _min && 'Date'[Date] <= _max))
//
Measure 2(y) =
var _max = maxx(ALLSELECTED('Table'[Date]), 'Table'[Date])
var _min = minx(ALLSELECTED('Table'[Date]), 'Table'[Date])
return
CALCULATE(
sum('Table (2)'[Energy Consumption (kWh)]), FILTER('Date', 'Date'[Date] >= _min && 'Date'[Date] <= _max))
//
Measure 1(x) =
var _max = maxx(ALLSELECTED('Table'[Date]), 'Table'[Date])
var _min = minx(ALLSELECTED('Table'[Date]), 'Table'[Date])
return
CALCULATE(
sum('Table'[Air Temperature (degC)]), FILTER('Date', 'Date'[Date] >= _min && 'Date'[Date] <= _max))
//
Measure 2(x) =
var _max = maxx(ALLSELECTED('Table'[Date]), 'Table'[Date])
var _min = minx(ALLSELECTED('Table'[Date]), 'Table'[Date])
return
CALCULATE(
sum('Table (2)'[Air Temperature (degC)]), FILTER('Date', 'Date'[Date] >= _min && 'Date'[Date] <= _max))
//

Sorry but I cannot attach the pbix file.

Thank you again.