cancel
Showing results for
Did you mean:
New Member

## Find out unique serial number and count

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

1 ACCEPTED SOLUTION
Resolver I

Hi @stephenl ,

``````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 )
)``````

7 REPLIES 7
Resolver I

Hi @stephenl ,

``````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 )
)``````

Community Champion

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.

New Member

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.

Super User

try this

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
New Member

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.

Community Support

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.

Super User

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

do not hesitate to give a kudo to useful posts and mark solutions as solution

Announcements

#### Launching new user group features

Learn how to create your own user groups today!