Forum Discussion

YeonseokKim's avatar
YeonseokKim
Frequent Visitor
4 years ago

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 ...
YeonseokKim's avatar
YeonseokKim
Frequent Visitor
4 years ago

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's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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