cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Francesco Frequent Visitor
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

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Data filtering by table values

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!

4 REPLIES 4
Phil_Seamark Super Contributor
Super Contributor

Re: Data filtering by table values

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!

Francesco Frequent Visitor
Frequent Visitor

Re: Data filtering by table values

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 Super Contributor
Super Contributor

Re: Data filtering by table values

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!

Francesco Frequent Visitor
Frequent Visitor

Re: Data filtering by table values

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
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 41 members 965 guests
Please welcome our newest community members: