cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manuelfigueirat
Frequent Visitor

Multiple aggregation Distinct Count (3 Filters)

Hello Everyone,

 

I´m trying to get to count (distinct) how many times a Pallet Code was used multiple times for different deliveries within the same shipment Number --> A shipment can contain multiple Deliveries and the deliveries can have the same pallet code

 

Shipment NumberDelivery NumberPallet CodeDISTINCTCOUNT Expected Output
23485553
23465553
23455553

 


The example above contains one Shipment Number (555) with multiple deverlies where they all used the same Pallet Code, menaing the pallet code has been used 3 times within the same Shipment and multiple deliveries

 

I used this, but seems not working well:

 

 

 

 

Countv1 = COUNTROWS(FILTER(SSCC, SSCC[Shipment Number] = EARLIER(SSCC[Shipment Number]) && SSCC[Pallet Code] = EARLIER(SSCC[Pallet Code])))

 

 

 

 

Many thanks!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@manuelfigueirat , if this is a column, this seems fine

 

countx(filter( SSCC, SSCC[Shipment Number] = EARLIER(SSCC[Shipment Number]) && SSCC[Pallet Code] = EARLIER(SSCC[Pallet Code])),[Shipment Number])

 

for measure

 

countx(filter(allselected( SSCC), SSCC[Shipment Number] = MAX(SSCC[Shipment Number]) && SSCC[Pallet Code] = MAX(SSCC[Pallet Code])),[Shipment Number])

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@manuelfigueirat , if this is a column, this seems fine

 

countx(filter( SSCC, SSCC[Shipment Number] = EARLIER(SSCC[Shipment Number]) && SSCC[Pallet Code] = EARLIER(SSCC[Pallet Code])),[Shipment Number])

 

for measure

 

countx(filter(allselected( SSCC), SSCC[Shipment Number] = MAX(SSCC[Shipment Number]) && SSCC[Pallet Code] = MAX(SSCC[Pallet Code])),[Shipment Number])

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.