Forum Discussion
DISTINCTCOUNT function with many conditions
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 |
3 Replies
johnt75Super User
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 opportunityThanks 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
