Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Rune_
Frequent Visitor

Sum of values in one query based on filter from another Query

I have one query called

JobTasks with these columns

 

JobNo | JobTaskFrom | JobTaskTo

10100 | 10                | 20

 

I would like to add a Column called "Amount" to this Query that adds values from a query called

 

JobLedger

JobNo | JobTask | Amount

10100 | 11         | 50

10100 | 15         | 60

 

The filters for adding Amount is from the query JobTasks

JobTask >= then JobTaskFrom and <= then JobTaskTo

How do I write this function?

1 ACCEPTED SOLUTION
Smauro
Solution Sage
Solution Sage

Hi @Rune_ 

It's certainly not the quickest way, but you can try this:

 

 

 

    Amount = Table.AddColumn(PreviousStep, "Amount", each let no = [JobNo], from = [JobTaskFrom], to = [JobTaskTo] in List.Sum(Table.SelectRows(JobLedger, each [JobNo]=no and [JobTask]>=from and [JobTask]<=to)[Amount]), type number)

 

 

 

Where PreviousStep is the name of your last step.

Cheers


Edit:
This should be a bit quicker:

 

    #"Merge Queries" = Table.NestedJoin(PreviousStep, {"JobNo"}, Table.SelectColumns(JobLedger, {"JobNo", "JobTask", "Amount"}), {"JobNo"}, "JobLedger", JoinKind.LeftOuter),
    #"Added Amount" = Table.AddColumn(#"Merge Queries", "Amount", each let from = [JobTaskFrom], to = [JobTaskTo] in List.Sum(Table.SelectRows([JobLedger], each _[JobTask]>=from and _[JobTask]<=to)[Amount]), type number),
    #"Remove JobLedger" = Table.RemoveColumns(#"Added Amount", {"JobLedger"})

 

 




Feel free to connect with me:
LinkedIn

View solution in original post

6 REPLIES 6
mahoneypat
Employee
Employee

Here is another approach to consider:

 

  • merge the second query into the first one on JobNo, calling the new colum AmtTable (leave it in Table form)
  • add a custom column with this function: = List.Sum([AmtTable][Amount])

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Smauro
Solution Sage
Solution Sage

Hi @Rune_ 

It's certainly not the quickest way, but you can try this:

 

 

 

    Amount = Table.AddColumn(PreviousStep, "Amount", each let no = [JobNo], from = [JobTaskFrom], to = [JobTaskTo] in List.Sum(Table.SelectRows(JobLedger, each [JobNo]=no and [JobTask]>=from and [JobTask]<=to)[Amount]), type number)

 

 

 

Where PreviousStep is the name of your last step.

Cheers


Edit:
This should be a bit quicker:

 

    #"Merge Queries" = Table.NestedJoin(PreviousStep, {"JobNo"}, Table.SelectColumns(JobLedger, {"JobNo", "JobTask", "Amount"}), {"JobNo"}, "JobLedger", JoinKind.LeftOuter),
    #"Added Amount" = Table.AddColumn(#"Merge Queries", "Amount", each let from = [JobTaskFrom], to = [JobTaskTo] in List.Sum(Table.SelectRows([JobLedger], each _[JobTask]>=from and _[JobTask]<=to)[Amount]), type number),
    #"Remove JobLedger" = Table.RemoveColumns(#"Added Amount", {"JobLedger"})

 

 




Feel free to connect with me:
LinkedIn

Rune_
Frequent Visitor

Hi @Smauro 

 

Is there any way I can add a filter to this function

Amount = Table.AddColumn(PreviousStep, "Amount", each let no = [JobNo], from = [JobTaskFrom], to = [JobTaskTo] in List.Sum(Table.SelectRows(JobLedger, each [JobNo]=no and [JobTask]>=from and [JobTask]<=to)[Amount]), type number)

 

I have a column with dates and I want a filter on the dates that shows all dates < then EndOfMonth last month (example todays date is 23.06 then I would like it to show dates < then 31.05)  

Hi @Rune_ 

 

Assuming that your [Date] column is in 'JobLedger' then this should work:

Amount = Table.AddColumn(PreviousStep, "Amount", each let no = [JobNo], from = [JobTaskFrom], to = [JobTaskTo], d = Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())) in List.Sum(Table.SelectRows(JobLedger, each [Date]<d and [JobNo]=no and [JobTask]>=from and [JobTask]<=to)[Amount]), type number)

 




Feel free to connect with me:
LinkedIn

Rune_
Frequent Visitor

Thank you. 

This worked perfectly 😀

Rune_
Frequent Visitor

Hi @Smauro , 

 

Thank you for your quick reply. 

I used your first solutions "Amount = Table.AddColumn..." and it worked perfectly. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors