Distinct Count with Filter

[tulasik]

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','Measures - Revenue'[Revenue Total]>=0))

However it seems to count the ones without revenue as well.

Regards,

Tulasi

[v-lionel-msft]

Hi @tulasik ,

Is the 'Measures - Revenue'[Revenue Total] a measure? We must analyze the formula of this measure if we want to find the error.

The easiest way is to change this measure to the calculated column in 'Dimension - DMA Configs’ table.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@tulasik , Try like

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

Hope Revenue Total is column

[DataZoe]

@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!