Power Pivot or Power Query to show all claim lines for a customer with a specific event

[brawnystaff]

I have a spreadsheet with multiple claim lines per day for various events taking place per customer.

I am looking for either a Power Query or Power Pivot solution to show all claim lines for only dates where a particular event took place on one of the claim lines.

For example, if customer ID 1111A had event "45456A" take place on 1/1/2012, I want to see all claim lines for 1/1/2012 for that customer. But, if on 4/1/2012 customer 1111A had five claim lines for that day with no events equaled 45456A, then I want none of those claim lines to appear.

Using Excel 365 and tried using a PowerPivot measure to flag rows, but no luck. Any ideas?

See link below for sample spreadsheet. Thanks.

https://drive.google.com/file/d/1oFYw9Z47ZpuGgb6mmwp1qbVcaEedwaOq/view?usp=sharing

 

[Fazza]

maybe this helps. This is one way to do it in a normal pivot table, or query table.

SELECT A.[Date], A.Customer, A.Event, A.[Amt Paid]
FROM [Data$] A
WHERE A.[Date] IN 
(SELECT DISTINCT B.[Date]
FROM [Data$] B
WHERE B.Event = '45456A')