Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have a rather tricky question I cannot solve myself.
I have this kind of recordings, which describe time slots :
Beginning | Category | Duration (hours) |
23/01/2020 09:00 | blabla | 6 |
23/01/2020 15:00 | blibli | 3 |
23/01/2020 18:00 | bloblo | 2 |
I have to create reports that only take 7.5 hours maximum per day so I would like to add a column that recalculates the durations so that their total reaches 7.5, starting with the first ones. Here's how it would look like :
Exemple :
Beginning | Category | Duration (hours) | Duration (2) |
23/01/2020 09:00 | blabla | 6 | 6 |
23/01/2020 15:00 | blibli | 3 | 1.5 |
23/01/2020 18:00 | bloblo | 2 | 0 |
I can do it by extracting the data and use Excel mais I would love to have it displayed directly in PowerBi.
Thank you for your help,
Solved! Go to Solution.
Hi @Anonymous,
In order to make it work, replace the bold part (it's my last step name).
Take a look at yours steps, get the name and replace it in the function.
If possible share you m code, so I can replace it with your last step name.
Hello,
thank you so much for your answer. DAX is ideed an easier way for me to complete the task.
I dont have an index so I tested on the beginning date this way :
Measure =
var _previousduration= CALCULATETABLE(VALUES('Table'[Duration (hours)]),FILTER(ALL('Table'),'Table'[Beginning]<MAX('Table'[Beginning])))Return
IF(SUMX(FILTER(ALL('Table'),'Table'[Beginning]<=MAX('Table'[Beginning])),'Table'[Duration (hours)])<=7.5,MAX('Table'[Duration (hours)]),7.5-SUMX(_previousduration,'Table'[Duration (hours)]))
Hi @Anonymous ,
You can add a criteria in the if function to achieve it.
Measure =
var _previousduration= CALCULATETABLE(VALUES('Table'[Duration (hours)]),FILTER(ALL('Table'),'Table'[Index]<MAX('Table'[Index])))Return
IF(MAX('Table'[Duration (hours)])>=7.5,7.5,IF(SUMX(FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])),'Table'[Duration (hours)])<=7.5,MAX('Table'[Duration (hours)]),7.5-SUMX(_previousduration,'Table'[Duration (hours)])))
Duration (2) =
IF('Table'[Measure]>0,'Table'[Measure],0)
Finally you will see:
For the modified .pbix file,pls click here.
Hi @Anonymous ,
You can also try to use dax expression to achieve it:
Create 2 measures as below:
Measure =
var _previousduration= CALCULATETABLE(VALUES('Table'[Duration (hours)]),FILTER(ALL('Table'),'Table'[Index]<MAX('Table'[Index])))Return
IF(SUMX(FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])),'Table'[Duration (hours)])<=7.5,MAX('Table'[Duration (hours)]),7.5-SUMX(_previousduration,'Table'[Duration (hours)]))
Duration (2) =
IF('Table'[Measure]>0,'Table'[Measure],0)
Finally you will see:
For the related .pbix file,pls click here.
Hello,
thank you so much for your answer. DAX is ideed an easier way for me to complete the task.
I dont have an index so I tested on the beginning date this way :
Measure =
var _previousduration= CALCULATETABLE(VALUES('Table'[Duration (hours)]),FILTER(ALL('Table'),'Table'[Beginning]<MAX('Table'[Beginning])))Return
IF(SUMX(FILTER(ALL('Table'),'Table'[Beginning]<=MAX('Table'[Beginning])),'Table'[Duration (hours)])<=7.5,MAX('Table'[Duration (hours)]),7.5-SUMX(_previousduration,'Table'[Duration (hours)]))
Hi @Anonymous ,
Try this code for a custom column on Power Query:
let
_dateTime = [Beginning],
_currentValue = [#"Duration (hours)"],
_day = Table.SelectRows(#"Changed Type",
each
Date.From([Beginning]) = Date.From(_dateTime) and
[Beginning] <= _dateTime
)[#"Duration (hours)"],
_result =
List.Accumulate(_day, 7.5, (s,c) => s - c) in
if _result > 0 then _currentValue
else if _result + _currentValue > 0 then _result + _currentValue
else 0
change the bold part for your last step name.
Hello, thank you very much for your quick answer.
I tried the solution but can't make it work.
I can add a custom column with the formula you provided but I don't understand what I should replace "changed type" with, nor do I get how to use the formula with "addTable" below.
Could you just be a bit more descriptive please ?
best regards
Hi @Anonymous,
In order to make it work, replace the bold part (it's my last step name).
Take a look at yours steps, get the name and replace it in the function.
If possible share you m code, so I can replace it with your last step name.
Hello Camargos,
I think your solution works. I will mark it as a solution as soon I have an answer to the question I asked to the second person that proposed me a solution (with DAX, which is a bit easier for the newbie I am).
Thank you again,
Hi @Anonymous ,
You can add a criteria in the if function to achieve it.
Measure =
var _previousduration= CALCULATETABLE(VALUES('Table'[Duration (hours)]),FILTER(ALL('Table'),'Table'[Index]<MAX('Table'[Index])))Return
IF(MAX('Table'[Duration (hours)])>=7.5,7.5,IF(SUMX(FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])),'Table'[Duration (hours)])<=7.5,MAX('Table'[Duration (hours)]),7.5-SUMX(_previousduration,'Table'[Duration (hours)])))
Duration (2) =
IF('Table'[Measure]>0,'Table'[Measure],0)
Finally you will see:
For the modified .pbix file,pls click here.
Hello
thank you very much. It works !
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |