Data filtering by table values

[Francesco]

Hi,

here is my question.

I have one data table with 2 columns:

1. timestamp column format dd/mm/yyyy hh:mm:ss    (steps of random seconds)
2. parameter values (integer number)

 I have created a second table with only one column:

1. timestamp format dd/mm/yyyy hh:mm:ss  (steps of  5 minutes)

I would like to create a two columns table:

1. timestamp column format dd/mm/yyyy hh:mm:ss    
2. parameter values (integer number)

with only the parameter values of the first table, whose timestamp is included in  the timestamps of the second table.

It is obvious that I am very new to Power Bi, so much thanks to those who will be so patience to help me!

Regards

[Phil_Seamark]

HI @Francesco

I'm not 100% sure I fully understand what you are after but here is a starter.

Please create the following Calculated Table in Power BI Desktop.

Just replace DataTbl and SecondTable with your table and column names.

This approach throws out (or ignores) and rows from DataTbl that do not find a matching TimeStamp in the 2nd Table

NewTable = SELECTCOLUMNS(
        FILTER(
            CROSSJOIN(DataTbl,SecondTable),
            'DataTbl'[TimeStamp] = 'SecondTable'[TimeStamp]
            ),
            "TimeStamp" , 'DataTbl'[TimeStamp] ,
            "Parameter" , 'DataTbl'[Parameter]
            )

[Francesco]

Hi Phil , thanks a lot for your answer that gave me a new way to solve the problem. But still it is not fully solved.

Using the measures:

Power_W = CALCULATE(SUM(DataTable[ParameterValues]);USERELATIONSHIP(DataTable[TimeStamp];TimeTable[TimeStamp]))

I can filter from DataTable al ParameterValues whose timestamp is included in TimeTable.

Instead I would like to have a table with all timestamps  of TimeTable[TimeStamp] and

ParameterValue = 0 if not found in DataTable at the related Timestamp.

ParameterValue  =  DataTable[ParameterValues]  if not found.

Regards.

[Phil_Seamark · Accepted solution]

You can add a calculated column to your DataTbl table which tests for the existance of the same timestamp in the SecondTable

and returns the Parameter Value if it finds on and 0 if it doesn't find a match.

I notice you SUM your ParameterValues, do you want to SUM if there are multiple matches?

New Column = IF(
                RELATED(SecondTable[TimeStamp])<>BLANK(),
                'DataTbl'[Parameter],
                0
                )

[Francesco]

Hi Phil,

I would like to add a column not to the DataTbl but to the one_column TimeStampTable.

If a value is found in the DataTable then copy it otherwise put zero.

The best should be not to manage one column for each parameter but all the DataTable parameter_columns at once.

p.s I used SUM as aggregate function, as there is a unique timestamp value, also VALUES can be used.

.