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 try to calculate the difference between row 1 and 2, 2 and 3, .... I have created an index column but it's not possible to work with. The field incidentid comes from a table (ActionLog) with several incident id and I have selected one.. Who can help ?
Solved! Go to Solution.
Hi @jackdan,
By my tests with the calculated column formula of adetogni, everything works as expected.
Please check if you have installed the latest version of Power BI Desktop firstly.
In addition, is the fields in the table visual come from the same table (ActionLog)?
You could have a reference of my test file to check if I have reproduced your scenario.
Best Regards,
Cherry
I want to find the calculate duration time by looking at the robot_action on the basis of robot_session (complete-start). However, I couldn't do it.
The yield is as follows.
My aim that in Power BI:
You can use this data_sheet:
id | robot_user | robot_id | robot_action | robot_session | action_value | robot_device | status | date |
14448 | xxx | 1 | start | 11577975085 | EKDW10S4M04897 | test | 02-01-20 | |
14447 | xxx | 1 | Complete | 11577975085 | EKDW10S4M04897 | init | 02-01-20 | |
14564 | xxx | 19 | Complete | 191578010500 | EKDW10S4M04897 | finished | 03-01-20 | |
14562 | xxx | 19 | Data saved | 191578010500 | OMR_Daily-BUD-3.01.2020.xlsx | EKDW10S4M04897 | running | 03-01-20 |
14563 | xxx | 19 | Mail sent | 191578010500 | EKDW10S4M04897 | running | 03-01-20 | |
14561 | xxx | 19 | start | 191578010500 | EKDW10S4M04897 | init | 03-01-20 |
Please help !
Hi @Anonymous ,
You may download my PBIX file from here.
Hope this helps.
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Let me know if I get it correctly.
All of these rows corresponds to the same Incident. You want to calculate Time of row where rankline = 2 minus time where rankline=1?
Is this correct?
And I guess you want it to have it as a calculated column...
Yes. It's exact. Also for the next ranklines : R3-R2, R4-R3, ... I work in Direct Query.
The only way to do it is by using EARLIER. Or by defining a var.
On top of my head and without actually trying it live (replace YOURTABLENAME with the actual table name)
1) Create a calculated column
2) in the formula type
TimeDifference=
VAR PreviousIncident = YOURTABLENAME[IncidentId]
VAR PreviousRank = YOURTABLENAME[RankId]-1
RETURN
YOURTABLENAME[TimeCommentAdded] -
SUMX(
FILTER(
YOURTABLENAME, YOURTABLENAME[IncidentId]=PreviousIncidentId && YOURTABLENAME[RankId]=PreviousRank)
,YOURTABLENAME[TimeCommentAdded])
The idea is that the function will run on each row. On a certain row will lookup the value of the incident and rank MINUS 1 of that row, then the return will calcualte the current time and then the SUMX will find a value of the same incidentId and rank and subtract the value.
You can achieve the same result with the EARLIER but it far more easier to understand with the variables .
Test it, shoudl work
If I type ActionLog[IncidentId] Power Bi doesn't recognize. I receive "Measure 4 = actionlog[incidentid]" with a red underline.
on the VAR line?
Yes.
Hi @jackdan,
By my tests with the calculated column formula of adetogni, everything works as expected.
Please check if you have installed the latest version of Power BI Desktop firstly.
In addition, is the fields in the table visual come from the same table (ActionLog)?
You could have a reference of my test file to check if I have reproduced your scenario.
Best Regards,
Cherry
I agree with you. It works but only if we work in import mode not in direct query. The index column isn't authorized in direct query. My question is : how to do in direct query ?
Thanks for your suggestion. I would try going through a SQL Query.
That shouldn't happen.
Make sure syntax is correct
Measurename =
VAR varname = calculation
RETURN
new calculation using the varname
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |