cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Take the first values up to a certain level

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.

3 ACCEPTED SOLUTIONS
Community Champion

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.

Proud to be a Super User!

Anonymous
Not applicable

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])))ReturnIF(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)]))`
It seemms to work except if I have only one task longer than 7.5h. In that case, the result is 0 (should be 7.5).
Is there any way you can think of that would solve the issue ?

Thank you

Community Support

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:

Best Regards,
Kelly
8 REPLIES 8
Community Support

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:

Best Regards,
Kelly

Anonymous
Not applicable

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])))ReturnIF(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)]))`
It seemms to work except if I have only one task longer than 7.5h. In that case, the result is 0 (should be 7.5).
Is there any way you can think of that would solve the issue ?

Thank you

Community Champion

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.

Proud to be a Super User!

Anonymous
Not applicable

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

Community Champion

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.

Proud to be a Super User!

Anonymous
Not applicable

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,

Community Support

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:

Best Regards,
Kelly
Anonymous
Not applicable

Hello

thank you very much. It works !

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.