Forum Discussion

Kagechiyo's avatar
Kagechiyo
New Member
5 years ago
Solved

Distinctcount by excluding column

Hi everybody,

this is my first post.

 

please help me,

i would like to distinct count vendorNo, with leadtime filter

here is my table

year, vendorNo, Account, Leadtime

2020000700099575700040001
2020000300087175200020001
2020000700031675700030001
2020000700031675200020011.5
2020000300255474100090002
2020000300087175200040012
2020000300255475700010002.33333333333333
2020000300087175700030002.35593220338983
2020000300255475200010002.5

 

and here is my DAX :

DAXcountofVendor =

VAR removeGL =
CALCULATE(
DISTINCTCOUNT('average lead time'[Vendor_No]),
FILTER('average lead time',
[AVGLT] <= 2 &&
[AVGLT] > 0
)
)

return
removeGL +0
 
Where AVGLT is the average of lead time.
 
but what do i get is 6
and i think it should be 4
 
is there any step(s) that i missed?
 
thank you in advance for your kindness
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Kagechiyo 

    Please correct me if I am wrong.

    I think the result should be 3.

     

    I am not sure about how you wrote your AVGLT measure, but please kindly check the below measure if it is suitable for you.

     

    DAXcountofVendor =
    VAR removeGL =
    FILTER (
    SUMMARIZE (
    'average lead time',
    'average lead time'[vendor_No],
    "avglt", AVERAGEX ( 'average lead time', 'average lead time'[Leadtime] )
    ),
    [avglt] > 0
    && [avglt] <= 2
    )
    RETURN
    COUNTROWS ( removeGL )

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

3 Replies

  • Kagechiyo's avatar
    Kagechiyo
    New Member
    5 years ago

     

    2020;0007000995;7570004000;1
    2020;0003003612;7520002000;1
    2020;0003003638;7570003000;1
    2020;0007000316;7520002001;1.5
    2020;0003002480;7410009000;2
    2020;0003000871;7520004001;2
    2020;0003002554;7570001000;2.33333333333333
    2020;0003000871;7570003000;2.35593220338983
    2020;0003002554;7520001000;2.5

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Kagechiyo 

    Please correct me if I am wrong.

    I think the result should be 3.

     

    I am not sure about how you wrote your AVGLT measure, but please kindly check the below measure if it is suitable for you.

     

    DAXcountofVendor =
    VAR removeGL =
    FILTER (
    SUMMARIZE (
    'average lead time',
    'average lead time'[vendor_No],
    "avglt", AVERAGEX ( 'average lead time', 'average lead time'[Leadtime] )
    ),
    [avglt] > 0
    && [avglt] <= 2
    )
    RETURN
    COUNTROWS ( removeGL )

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

    • Kagechiyo's avatar
      Kagechiyo
      New Member
      5 years ago

      thank you, Jihwan_Kim 
      after some modification i manage to get what i want..

       

      but i understand that there is another way to count the distinct.

      is by grouping.

       

      thanx=]