Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Francesco
Frequent Visitor

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

 

 

1 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
                )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

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]
            )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
                )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

 

.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.