cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
liey87 Frequent Visitor
Frequent Visitor

Get data within date time range on another table date time and duration

Hi,

 

I'm having a complex situation on how do I create another measure that will show range of data on Table A from date time on Table B which does not share any correlation name.

 

Table A : Contains the item details in terms of it testing time for every milliseconds. It is a record of data from

Table B: Contains the testing session and duration it takes for every test that having many testing features.Sample.png

 

 User require to prepare a new visual where user will select date range from TestingStartTime, StationID will be listing. After selecting StationID, a visual will visualize what data from Table A that running within TestingStartTime + Duration. How to write DAX that relate to other table based from timerange?

 

#EDITED

 

1. I'm getting data from Azure SQL using DirectQuery.

2. User require when they select Testing Session on Table B, it will listing data from Table A of what command and duration running on that time.

 

So I assume:-

- DateTime Start = TestStartTime

- DateTime End = TestStartTime + DurationRun(second)

- Result = TableA.ItemType, TableA.Command, TableA.Timestamp, TableA.Duration

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Get data within date time range on another table date time and duration

Hi @liey87,

I am afraid that you are not able to achieve the above requirement.

You want to select date time range to filter data. In your scenario, when you connect to the two tables from power BI Desktop, it will not display milliseconds in its original format, it displays time columns containing milliseconds to the following format.
2.PNG1.PNG

You would need to convert the time columns to whole number data type for this command  ‘TestStartTime + DurationRun(second)’  to make sense.
4.PNG


Thus, it is not possible to select a date time range in date slicer or filters in your current scenario, what other criteria do you want to set in this scenario? Please help to post expected result with providing multiple rows in two tables.


 

Thanks,
Lydia Zhang

 

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.