Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

az38
Community Champion
Community Champion

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

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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.