Forum Discussion
Creating Table with DAX from Multiple Tables
I am trying to utilize DAX to create a table (not power query), to help summarize the turnaround time between to services. Service 1 occures before Service 2. I ultimately want to know the time between the 1st time Service 2 starts for a customer [in a porgram] (minimum) from the most recent time Service 1 stopped (maximum & <Service 2 start). The other thing that must be considered, is the Status of both services must = Completed.
I know I have to start with summarizing the Service2 table, since that dictates if the Program/Customer combination is in the results table. I am currently using: SUMMARIZE(filter(Service2,Service2[Status]="Completed"),Service2[Program],Service2[Customer],"Service2_Start",min(Service2[Start])
However, I don't know how to bring in the maximum stop values from Service1 table that corresponds to the same Program/Customer, where Service 1 has a status = completed, and is less than Service2 Start time for that Program/Customer.
And then, I don't know how to add a calculated column using the datediff dax formula between the 2 time periods.
5 Replies
like that you need?
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhlKkoyP5UeW6piC_?e=4ZChbathank Ahmedx for your quick and detailed response. Also, the solution needs to take into consideration that the MAX service1[Stop] is not greater than service2[Start]. How do I encorporate that, another && in the Filter?
And if greater than service2[Start], what should be the result?
then that Service1[Stop] should not be considered. Only the max Service1[Stop] prior to Service2[start]. In real world terms, Service 2 can't start until Service 1 is complete, however there can be multiple Serivce 1 encounters that don't have anything to do with Service 2.
