Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a data table that gives us the ideal 'Start Time' and 'End Time' of a product's journey (common for all products) for a particular Mall (sample data below). Similarly, another data table has line wise data for each product, including product name, mall name & product collection time.
What I need is, that if a product belonging to a particular mall was collected up untill 10mins before the 'Start Time' in Table 1, then that Start Time is recorded, otherwise we get the next Start Time. The End Time corrosponding to the given Start Time should appear next to it. Refer the tables below:
I have the following data tables imported in Power BI Desktop:
Table1
Table 2
What I want to construct is the grid in table 3
Eg. For Ball belonging to mall A, Product Collection Time was 6:00, hence Start Time 10:00:00 got recorded, to which corrosponding End Time was 11:00:00. For Glove, however, Product Collection Time was 9:51:00 (less than 10mins from the Start Time), hence we moved onto the next Time Range in Mall A (11:00 to 12:30). Similarly, I need for Mall B and so on (all in one grid).
Would be obliged if someone could help!
Solved! Go to Solution.
Hi @siddharth3101_ ,
We can create two calcualted columns in table 2 as below.
start time = var a = 'Table 2'[Product Movement Time] + TIME(0,10,0) return CALCULATE(MIN('Table 1'[Start Time]),FILTER('Table 1','Table 1'[Mall] = 'Table 2'[Mall] && 'Table 1'[Start Time]>=a))
endtime = LOOKUPVALUE('Table 1'[End Time],'Table 1'[Start Time],'Table 2'[start time],'Table 1'[Mall],'Table 2'[Mall])
Pbix as attached.
Hi @siddharth3101_ ,
We can create two calcualted columns in table 2 as below.
start time = var a = 'Table 2'[Product Movement Time] + TIME(0,10,0) return CALCULATE(MIN('Table 1'[Start Time]),FILTER('Table 1','Table 1'[Mall] = 'Table 2'[Mall] && 'Table 1'[Start Time]>=a))
endtime = LOOKUPVALUE('Table 1'[End Time],'Table 1'[Start Time],'Table 2'[start time],'Table 1'[Mall],'Table 2'[Mall])
Pbix as attached.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |