Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Very beginner with PowerBI, i'm trying to compare two dates to determine some elapse time.
two tables : incidents and incident-sla
The tables have a relationship based on incident number (Number).
What i tryed with add a new column : Column = DATEDIFF(Incidents[Opened];'Incident-sla'[start time];DAY)
but i have the error : a single value for column 'start time' in table incident-sla cannot be determined....
Any help appreciated 🙂
++
Solved! Go to Solution.
upload to onedrive and include a link
Proud to be a Super User!
Hi @hakimissimo,
I have fixed your Calc for you and uploaded a new file to the share you created.
Steps:
- Change relationship direction from 'both' to 'single'
- updated the formula to the following, which returns the max('incident-sla'[Start time]) where the 'incident-sla'[Number] = 'Incidents'[Number].
BeforeAck = CALCULATE(max('incident-sla'[Start time]), FILTER('incident-sla', 'incident-sla'[Number] = 'Incidents'[Number]))
- Add a Table visual that show how the max value is returned for each rown in the header and detail tables
Proud to be a Super User!
Hello, I have the same Issue.
Trying to subtract one date from another.
CRM Opportunity entity has one record, the secondary table has multiple records.
Not having any luck, and am a little confused about the DAX Syntax.
Thius doesn't work, probably because the new_fixuplists has more than one re
StartupDays = DATEDIFF(opportunities[new_bookeddate], new_fixuplists[new_startupmeetingdate],DAY) |
Hello,
Very beginner with PowerBI, i'm trying to compare two dates to determine some elapse time.
two tables : incidents and incident-sla
The tables have a relationship based on incident number (Number).
What i tryed with add a new column : Column = DATEDIFF(Incidents[Opened];'Incident-sla'[start time];DAY)
but i have the error : a single value for column 'start time' in table incident-sla cannot be determined....
Any help appreciated 🙂
++
Hi @hakimissimo
In calculated column, we should use function “Related” if the “Start time” in the 1-side of the relationship (1:1, 1:* or *:1). Please check it out and try this formula.
Column = DATEDIFF ( Incidents[Opened]; RELATED ( 'Incident-sla'[start time] ); DAY )
Best Regards!
Dale
Hi Dale,
Thank you for quick feedback, it's really appreciated.
I have modified my request as you suggested.
Now i have the error : 'incident-sla[Start time]' either does not exist or does'nt have the relationshipe to any table...."
I have cheched the relationship and there is a many to 1 (please see attached image)... any ideas?
The related function relates the many side to the side, if you create your column in the incident-sla table your function will work something like
Column = DATEDIFF ( 'Incident-sla'[start time], RELATED ( Incidents[Opened] ), DAY )
It does not really make sense to create the column on the one side on the relationship as you would not know which of the many [start time] values to use for the date diff to the [opened].
If you are trying to use the max or min value from the many side to get the last/first value on the one side, then you could use the lookupvalue function within a calculate function function.
Hope that helps
Proud to be a Super User!
Hi Richbenmintz,
First thank you for feedback.
What i'm trying to do is basically to compare Start time with Oponed date for each incident (the moment where somebody is working on the resolution).
The tricky point is for a same incident 123456, there could be two start time dates (when ticket is going from support group 1 to support group2...)
Ideally i would like to retrieve the most recent date (because previous dates became obsolete).
I am not familiar with PowerBI and i do not know how to use lookupvalue function.
Hope to read you soon.
++
Hi @hakimissimo,
please provide a sample pbix file and I can help with the formula
Proud to be a Super User!
Hi Richbenmintz,
How can i attach my pbix file to the post?? (very newbie 🙂
++
upload to onedrive and include a link
Proud to be a Super User!
I have uploaded pbix file on my g drive : https://drive.google.com/drive/folders/0B74jBoLmjb1yTkktS2tONC1WM00
ask access please.
Hi @hakimissimo,
I have fixed your Calc for you and uploaded a new file to the share you created.
Steps:
- Change relationship direction from 'both' to 'single'
- updated the formula to the following, which returns the max('incident-sla'[Start time]) where the 'incident-sla'[Number] = 'Incidents'[Number].
BeforeAck = CALCULATE(max('incident-sla'[Start time]), FILTER('incident-sla', 'incident-sla'[Number] = 'Incidents'[Number]))
- Add a Table visual that show how the max value is returned for each rown in the header and detail tables
Proud to be a Super User!
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |