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

brawnystaff

Board Regular
Joined
Aug 9, 2012
Messages
81
Office Version
365
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,350
maybe this helps. This is one way to do it in a normal pivot table, or query table.

Code:
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')
 

Watch MrExcel Video

Forum statistics

Threads
1,099,362
Messages
5,468,180
Members
406,569
Latest member
Quest_

This Week's Hot Topics

Top