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.
I cannot manipulate this data much because of Direct query so need to use a dax measure.
I have a Status_History table and if you look at one ID it looks like this:
ID | STATUS | STATUS CHANGE DATE |
1 | Inspection Due | Tue Jan 10 2017 18:46:53 |
1 | Inspection Pending | Tue Jan 11 2017 6:30:53 |
1 | Submitted For Review | Tue Jan 11 2017 9:34:04 |
1 | Billing | Tue Jan 11 2017 20:22:15 |
I would like to count the time in minutes only if the record moves from Inspection Due to Inspection Pending, sometimes it can skip Status and I do not want to count those. The problem, I cannot get the STATUS CHANGE DATE to return the date and time stamp, it will only return a date. In my STATUS_HISTORY table, the STATUS CHANGE DATE is of data type Date/Time and I have also tried changing to different formats (3/13/2001 1:20:55 pm). I have tried using MAX, MIN, SELECTEDVALUE, and a few others but everything will only return me the Date not the time as well.
Something like this:
If I could figure out this bug I think I could get the logic.
Solved! Go to Solution.
@Anonymous , Try a mesure like
averageX(values(Table[ID]), datediff(calculate(Min(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = "Inspection Due")) ,calculate(Max(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = " Inspection Pending")),day))
or
averageX(values(Table[ID]), datediff(calculate(Min(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = "Inspection Due")) ,calculate(Max(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = " Inspection Pending")),Minute))
or
Sumx(values(Table[ID]), datediff(calculate(Min(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = "Inspection Due")) ,calculate(Max(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = " Inspection Pending")),Minute))
Hi @Anonymous
Have you solved your problem? If yes, kindly accept it as the solution. Otherwise, please provide more details about your problem so that we can work on it further. Thanks.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.
@Anonymous , Try a mesure like
averageX(values(Table[ID]), datediff(calculate(Min(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = "Inspection Due")) ,calculate(Max(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = " Inspection Pending")),day))
or
averageX(values(Table[ID]), datediff(calculate(Min(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = "Inspection Due")) ,calculate(Max(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = " Inspection Pending")),Minute))
or
Sumx(values(Table[ID]), datediff(calculate(Min(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = "Inspection Due")) ,calculate(Max(Table[STATUS CHANGE DATE]), filter(Table, Table[STATUS] = " Inspection Pending")),Minute))
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 |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |