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.
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?
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
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.
You would need to convert the time columns to whole number data type for this command ‘TestStartTime + DurationRun(second)’ to make sense.
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.