Forum Discussion
Data filtering by table values
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
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 )
4 Replies
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] )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.
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 )
