topic Re: DISTINCTCOUNT function with many conditions in DAX Commands and Tips

DISTINCTCOUNT function with many conditions

YeonseokKim — Mon, 25 Jul 2022 08:21:20 GMT

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

Re: DISTINCTCOUNT function with many conditions

johnt75 — Mon, 25 Jul 2022 09:48:13 GMT

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

Re: DISTINCTCOUNT function with many conditions

YeonseokKim — Tue, 26 Jul 2022 01:03:34 GMT

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.

Re: DISTINCTCOUNT function with many conditions

johnt75 — Tue, 26 Jul 2022 08:36:57 GMT

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