Forum Discussion

Francesco's avatar
Francesco
Frequent Visitor
9 years ago
Solved

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 creat...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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
                )
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
    Francesco
    Frequent Visitor
    9 years ago

    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's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      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's avatar
        Francesco
        Frequent Visitor
        9 years ago

        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.

         

        .