Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Distinct Count - DAX Check Needed

I have 2 columns: ZH# + ZH Versions. ZH# is the item number. ZH Version is the version iteration of the ZH#.    PROBLEM TO SOLVE = How can I get a distinct count of all the ZH# + take into consider...
Need Help
Tutorial Requests
  • Stachu's avatar
    Stachu
    7 years ago

    I don't get the logic exactly

     

    ZH#DistinctCount = DISTINCTCOUNT(ZH[ZH#])

     

    returns 7, which is including ZH# 30 which has version 1.3B, which is also the only version for ZH# 30
    so it seems to me that output should be 8 not 9, correct?

    if you want to count multiple verisons within same ZH# I would probably do something like this

    Measure = COUNTROWS ( SUMMARIZE ( ZH, ZH[ZH#], ZH[ZH Ver.] ) )

     

Stachu's avatar
Stachu
Community Champion
7 years ago

I don't get the logic exactly

 

ZH#DistinctCount = DISTINCTCOUNT(ZH[ZH#])

 

returns 7, which is including ZH# 30 which has version 1.3B, which is also the only version for ZH# 30
so it seems to me that output should be 8 not 9, correct?

if you want to count multiple verisons within same ZH# I would probably do something like this

Measure = COUNTROWS ( SUMMARIZE ( ZH, ZH[ZH#], ZH[ZH Ver.] ) )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Stachu

     

    Thank you for your response! Your DAX measure to count multiple versions of ZH# worked! I added your suggestion to my current DAX and it worked: 

     

     

    ZHReplacementCount =
    SUMX(
    VALUES( Programs_Machine[MACHINE_L_NUMBER] ),
    CALCULATE(
    COUNTROWS(
    SUMMARIZE(
    ZH, ZH[ZH#], ZH[ZH Ver.]
    )
    )
    )
    )
     
    To answer your question, each ZH# has a version. ZH Ver. = 0 is a version. So when we look at all of the distinct ZH#'s, there are only 7. But when we take into account the different ZH Versions, there are 2 that were missing from the count and that was my goal to solve. 
     
    Thank you for your support!