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

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