Forum Discussion

Mike_'s avatar
Mike_
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 create a scatter plot with two series of data. Both of them to include different sub-sets of the same one data source table. The split into the subsets might vary each time a calculation is ran. In the below data, for example, when the analysis is ran, the comparison would be between:

(i) 1/6/2022 - 7/6/2022 and (ii) 8/6/2022 - 14/06/2022.

 

Next month's analysis might be between:

(i) 5/7/2022 - 15/7/2022 and (ii) 16/7/2022 - 30/07/2022

or even between:

(i) 20/12/2021 - 20/01/2022 and (ii) 20/12/2020 - 20/01/2021.

 

The criteria of the split needs be based on the dates, but the split date will be known after the data is available, therefore I cannot set a standard filter or slicer.

 

The other main issue, apart from the manual splitting, is when trying to combine these two sets into one same scatter plot.

 

I suppose the reason is because we need to create another series, in the Power Query Editor, duplicating the original series. However, even in this case, could we apply different date ranges, for each series, in the same scatter plot ?

 

Below you may find the example data and how the ideal scatter plot looks like in MS Excel ( Of course, in order to create this in Excel, I needed to duplicate the initial table, and filter manually the new tables, so that in the final scatter plot, the series are shown in different colours.

 

Date / Air Temperature (degC) / Energy Consumption (kWh)
01/06/202232100
02/06/202233100.5
03/06/202234101
04/06/202235101.5
05/06/202236102
06/06/202237102.5
07/06/202238103
08/06/202240105
09/06/202242107
10/06/202244109
11/06/202246111
12/06/202248113
13/06/202250116
14/06/202252120
 

 

All Data

 

All Data split into two new tables

 

Final Scatter Plot comparing the two sub-sets of the data

 

Example of the Power BI file created with each scatter plot retrieving data from another table and no relationship between the two identical tables :

 

 

 

Not sure if this is doable, however looking forward to your valuable advice. 

 

Thank you very much in advance.

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    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_'s avatar
      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.