Distinct Count with filter

[AriGooner]

Hi Everyone, 

Can somebody help me 

So I have a case like this, I'm trying to find a unique value from the table,
I'm trying to count every outlet code whose total sales are more than 0 (zero) then it is counted as 1,
otherwise if each code is not more than 0 then it is 0. the code i use is for active outlet

= CALCULATE(DISTINCTCOUNT(
(tabletrx[Kode Outlet]))
,tabletrx[Hl]>0)

But the problem is when the "kode outlet" have 2 transaction with value
example 1 and -2 still count as 1 but what i hope is 0 cause 1 - 2 = -1

[Jos_Woolley]

Hi,

Post a small example dataset together with expected result.

Regards

[AriGooner]

this is  simple dataset and what i expected for example.

so what i expect is every total qty of outlets is greater than 0 then outlet is counted as 1.

[Samarth_18]

@AriGooner Please try this:-

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table (4)'[Outlet Code] ),
    FILTER ( 'Table (4)', SUM ( 'Table (4)'[QTY] ) > 1 )
)

Output:-

Thanks,

Samarth

[AriGooner]

the result still not what i expect 

i try this,
= CALCULATE(DISTINCTCOUNT(tabletrx[Kode Outlet]),filter(tabletrx,tabletrx[Hl]>1))

but the result is same like
= CALCULATE(DISTINCTCOUNT(tabletrx[Kode Outlet]))

[Samarth_18]

@AriGooner Use it like this:-

= CALCULATE(DISTINCTCOUNT(tabletrx[Kode Outlet]),filter(tabletrx,sum(tabletrx[Hl])>1))

[AriGooner]

still not working for me 😞

[v-yanjiang-msft]

Hi @AriGooner ,

According to your description, here's my solution.

This is my sample data.

Create a measure.

Active outlet = 
IF (
    SUMX (
        FILTER (
            ALL ( 'tabletrx' ),
            'tabletrx'[Kode Outlet] = MAX ( 'tabletrx'[Kode Outlet] )
        ),
        'tabletrx'[Hl]
    ) > 0,
    1,
    BLANK ()
)

Get the correct result.

If you want the total to be 2, use a calculated column instead.

Column = 
IF (
    SUMX (
        FILTER (
            ALL ( 'tabletrx' ),
            'tabletrx'[Kode Outlet] = EARLIER ( 'tabletrx'[Kode Outlet] )
        ),
        'tabletrx'[Hl]
    ) > 0
        && 'tabletrx'[Hl] > 0,
    1,
    BLANK ()
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[AriGooner]

hi, i try your measure but why in my file for EARLIER is not working? may i am wrong? i already update my power bi in the desktop

[AriGooner]

Hi, Thanks for your advice.

i try your measure but in my data number less than 0 still counting as 1?

may im wrong?

[v-yanjiang-msft]

Hi @AriGooner ,

How about only put Kode Outlet and HI in a table visual like me?

You can analyze why the result is wrong, it should be right as usual, compare your sample with mine or post the sample without sensitive information.

Best Regards,
Community Support Team _ kalyj

[Samarth_18]

Hi @AriGooner ,

Please try this:-

= CALCULATE(DISTINCTCOUNT(tabletrx[Kode Outlet]),SUM(tabletrx[Hl])>0)

Thanks,

Samarth

[AriGooner]

Thanks for advice, if i add SUM the measure can't working.