cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stephenl
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 DateSerial NumberSales Type
2020-02-09C2FE352BAB
2020-02-09C2FE352BP
2020-02-09C2FE352BP
2020-02-09C2FE352BMB
2020-02-09AB6D3AF3B
2020-02-09925CD7DFCD
2020-02-09195B55CABSM
2020-02-09195B55CAB
2020-02-092548D686BBB
2020-02-092548D686B
2020-02-092548D686BB
2020-02-09339506B4ZC
2020-02-09339506B4C

 

The expected result is : 

 

AB6D3AF3
2548D686

 

Count = 2

1 ACCEPTED SOLUTION
Frank_Fei
Resolver I
Resolver I

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

 

View solution in original post

7 REPLIES 7
Frank_Fei
Resolver I
Resolver I

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

 

View solution in original post

Mariusz
Community Champion
Community Champion

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


 

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.

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

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

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
Community Support
Community Support

Hi @stephenl ,

Let me know whether you want the result like this:

table result.png

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
Super User
Super User

@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

 

Снимок.PNG

 


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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.