Find out unique serial number and count

[stephenl]

Hi,

I want to find out the list of unique Serial number that have "B" in Sales Type, and the count of the serial number that fit this conditons.   Can anyone please advise how to write the measure/DAX?  Thanks in advance.

Transaction Date	Serial Number	Sales Type
2020-02-09	C2FE352B	AB
2020-02-09	C2FE352B	P
2020-02-09	C2FE352B	P
2020-02-09	C2FE352B	MB
2020-02-09	AB6D3AF3	B
2020-02-09	925CD7DF	CD
2020-02-09	195B55CA	BSM
2020-02-09	195B55CA	B
2020-02-09	2548D686	BBB
2020-02-09	2548D686	B
2020-02-09	2548D686	BB
2020-02-09	339506B4	ZC
2020-02-09	339506B4	C

The expected result is : 

AB6D3AF3

2548D686

Count = 2

[Anonymous · Accepted solution]

Hi @stephenl ,

Please try this one.

M 2 = 
VAR K =
    FILTER ( Tab, Tab[Sales Type] IN { "B", "BB", "BBB", "BBBB" } )
VAR rk =
    CALCULATETABLE (
        VALUES ( Tab[Serial Number] ),
        FILTER ( Tab, NOT ( Tab[Sales Type] IN { "B", "BB", "BBB", "BBBB" } ) )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Tab[Serial Number] ),
        FILTER ( Tab, NOT ( Tab[Serial Number] IN rk ) ),
        KEEPFILTERS ( K )
    )

[Mariusz]

Hi @stephenl 

Sure, try this

Measure = 
CALCULATE(
    DISTINCTCOUNT( 'Table'[Serial Number] ),
    'Table'[Sales Type] = "B"
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[stephenl]

Hi both,

Thanks for the prompt resopnse.  Sorry that I didn't state my question clear.

I want the serial number that only have sales with "B" in sales type, whether it is one "B' or multiple "B" is fine.  I don't want to include any serial number that have other thing.  For example : C2FE352B should not be in the list as it has sales types with M and MB.

Thanks in advance.

[az38]

@stephenl 

try this

Measure = 
CALCULATE(
SUMX(
    FILTER('Table','Table'[Sales Type]="B"),
    IF(CALCULATE(DISTINCTCOUNT('Table'[Serial Number]), 'Table'[Sales Type]="B")=1, 1, 0)
))

[stephenl]

Hi az38,

Thanks.  No error message shows.  Can you advise how to show the list of the serial number please?  I try to put this new "measured" field in matrix, but not successful.

In addition, do I use "OR" to include "B", "BB", "BBB",...etc in the filter statement please?  Any smarter way to do it?  Ultimately, I want to have the list of serial number that have sales type with "B"s only, whether "B", "BB","BBB" does not matter.  As long as the serial number does not have sales with non "B" in there.

thanks in advance.

[v-yingjl]

Hi @stephenl ,

Let me know whether you want the result like this:

If so, create a control measure like this, put it into the table visual filter and set its value as 1:

Control measure =
VAR _type =
    SELECTEDVALUE ( 'Table'[Sales Type] )
RETURN
    IF ( LEFT ( _type, 1 ) = "B", 1, 0 )

Create a count measure like @ az38 mentioned  to calculate:

Count = 
SUMX (
    FILTER ( 'Table', LEFT ( 'Table'[Sales Type], 1 ) = "B" ),
    IF (
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Serial Number] ),
            LEFT ( 'Table'[Sales Type], 1 ) = "B"
        ) = 1,
        1,
        0
    )
)

Best Regards,
Yingjie Li

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

[az38]

@stephenl 

Just put it in visual and filter by Sales type like below and create a measure

Measure = calculate(DISTINCTCOUNT('Table'[Serial Number]), ALL('Table'), 'Table'[Sales Type]="B")

Bytheway, you have 3 unique S/N in your data example, not 2