DISTINCTCOUNT function with many conditions

[YeonseokKim]

I want to calcualte DISTINCTCOUNT( 'shipping data'[Loaing #]) with many conditions. 

I can get the # of trips of each route easily. But, I want to get the number of backhaul opportunities of the route.

For example, At 7/1, The number of trips of A->B route is 3.

And the Backhaul opportunutes of the route(B->A) is 5. Because  trips of B->A route is total 5 at 7/1.

In addition, Final opportunity number will be just 3 because the opportunity is less than the number of trips of the route at the same day. 

7/1 = A-> B route

Actual trip = 3

Backhaul opportunity = 3

I have numerous daily shipping data and I want to get exact number of backhaul opportunities. 

Please refer to the table below and give me advice how should I use DAX function for it

Table='shipping data'

Date	Route	Loading #	Item
2022-07-01	A->B	46912042	PET
2022-07-01	A->B	46912042	Keg
2022-07-01	A->B	46912043	PET
2022-07-01	A->B	46912044	bottle
2022-07-01	B->A	46912045	Keg
2022-07-01	B->A	46912045	PET
2022-07-01	B->A	46912045	bottle
2022-07-01	B->A	46912046	bottle
2022-07-01	B->A	46912047	bottle
2022-07-01	B->A	46912048	bottle
2022-07-01	B->A	46912049	bottle
2022-07-01	A->C	46912050	bottle
2022-07-01	A->C	46912051	bottle
2022-07-02	A->B	46912052	PET
2022-07-02	A->B	46912053	Keg
2022-07-02	A->B	46912054	PET
2022-07-02	B->A	46912055	bottle
2022-07-02	A->C	46912056	Keg
2022-07-02	A->B	46912057	PET
2022-07-02	A->B	46912058	bottle
2022-07-02	A->B	46912059	bottle
2022-07-02	B->A	46912060	bottle
2022-07-02	A->C	46912061	Keg
2022-07-02	A->B	46912062	PET
2022-07-02	A->B	46912063	bottle
2022-07-02	A->B	46912064	bottle
2022-07-02	B->A	46912065	PET
2022-07-02	A->C	46912066	bottle
2022-07-02	A->B	46912067	bottle

[YeonseokKim]

Thanks for your solution!

I appreciate it. 

But, I have more questions...

I want to visualize the opportunity & Actual trips in the same graph.

The graph format will be like below

In addition, I want to show the opportunity & real trips by each route, the graph like below.

The point to be emphasized here is that all opportinites should be summed up by adding each opportunity per day

(EX. 7/4

A->B = 7

B->A = 4

Then the opportunity of A->B = 4

7/5

A->B = 6

B->A  = 7

Then the opportunity of A->B = 6

Finally, Total opportunity of 7/4&7/5 = 4+6 = 10 (Not 11)

It would be very grateful if you could give me advice. 

[johnt75]

Try the below

Backhaul opportunity = 
var source = SELECTEDVALUE('Table'[Source])
var dest = SELECTEDVALUE('Table'[Dest])
var actualTrips = SUMX( VALUES('Date'[Date]), CALCULATE(COUNTROWS( 'Table' ))
var returnTrips = SUMX( VALUES('Date'[Date]), CALCULATE( COUNTROWS( 'Table' ),
    REMOVEFILTERS('Table'[Route]),
    TREATAS( { ( source, dest ) },'Table'[Dest],'Table'[Source] )
) )
var opportunity = MIN( actualTrips, returnTrips )
return opportunity 

[johnt75]

If you create 2 new columns, Source and Destination, by extracting the text before / after delimiter in Power Query, then you could create a measure like

Backhaul opportunity = 
var source = SELECTEDVALUE('Table'[Source])
var dest = SELECTEDVALUE('Table'[Dest])
var actualTrips = COUNTROWS( 'Table' )
var returnTrips = CALCULATE( COUNTROWS( 'Table' ),
    REMOVEFILTERS('Table'[Route]),
    TREATAS( { ( source, dest ) },'Table'[Dest],'Table'[Source] )
)
var opportunity = MIN( actualTrips, returnTrips )
return opportunity