Finding Invoices Containing A Product

Matt Allington shows how to use Power Query to get details on a top-level item (including all child items) which contains a particular child item:

As you can see in the image below, when a slicer is added to the product table and then you select an item in the slicer, the invoice detail table ONLY shows that single selected product.  But the requirement it to see ALL the items on ALL the invoices that contain that selected product.

Read on, as the solution definitely isn’t trivial.

Related Posts

Power Query FILTER()

Rob Collie takes us through a good use of FILTER() in DAX: The thing both of those formulas have in common is that they are using a measure in the filter argument of the CALCULATE function.  In both examples here, I’ve highlighted the offending measure in yellow. CALCULATE([Sightings per Year], [Avg Sighting Length in Mins]>6) CALCULATE([Sightings […]

Read More

Removing Text Between Delimiters in Power Query

Imke Feldmann has a new M function for us: While there is a native function to fetch text between 2 delimiters in Power Query, there is no such function that removes the text instead. Therefore I’ve created a custom function Text.RemoveBetweenDelimiter. It even lets you choose to remove the delimiters themselves as well via the […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031