Forum Discussion

tulasik's avatar
tulasik
Frequent Visitor
6 years ago

Distinct Count with Filter

Hi Everyone, I am trying to calculate distinct count with filters. I have used the formula:  SoldOC = CALCULATE(DISTINCTCOUNT('Dimension - DMA Configs'[Order Code]),FILTER('Measures - Revenue','Me...
Need Help
DataZoe's avatar
DataZoe
Microsoft Employee
6 years ago

tulasik You could try this adjustment to your measure:

SoldOC =
CALCULATE (
    DISTINCTCOUNT ( 'Dimension - DMA Configs'[Order Code] ),
    FILTER (
        'Measures - Revenue',
        'Measures - Revenue'[Revenue Total] >= 0
            && ISBLANK ( 'Measures - Revenue'[Revenue Total] ) = FALSE
    )
)

 

or this one:

 

SoldOC =
IF (
    ISBLANK ( 'Measures - Revenue'[Revenue Total] ),
    BLANK (),
    CALCULATE (
        DISTINCTCOUNT ( 'Dimension - DMA Configs'[Order Code] ),
        FILTER ( 'Measures - Revenue', 'Measures - Revenue'[Revenue Total] >= 0 )
    )
)

 

Hope it helps!