Distinct Count - DAX Check Needed

[Anonymous]

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 consideration a different ZH Version?

- I currently can successfuly get the distinct count of ZH# but I cannot figure out how to add another filter to my DAX that will take into consideration if there is a different ZH Version used with the same ZH# (please see screenshot below).

Current Output = 7 -> This result is from distinct count of the ZH#

Desired Output = 9 -> This desired output will count all of the distinct ZH# + any other ZH Versions  (please see screenshots below...the red arrows will highlight the 2 missing items that should be counted)

Current DAX:

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

ZHReplacementCount =

SUMX( VALUES( Programs_Machine[MACHINE_L_NUMBER] ),

  CALCULATE( ZH[ZH#DistinctCount])

)

I know my current ZHReplacementCount DAX does not take into account the ZH Versions, but I am not sure on how to incorporate it?

Any assistance will be greatly appreciated!

[AlB]

Hi  @Anonymous

You can do that count taking the version into account like this:

NewMeasure =
COUNTROWS ( SUMMARIZECOLUMNS ( ZH[ZH#], ZH[ZH Ver.] ) )

If you're going to use this in the SUMX, I guess (you're omitting crucial info on the structure of your tables and existing relationships) you'd need something like this: 

ZHReplacementCount_V2 =
SUMX ( VALUES ( Programs_Machine[MACHINE_L_NUMBER] ), [NewMeasure] )

Your explanation is quite good but you should include the details of the tables involved and their relationships. Plus please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test (which I haven't been able to do)

[Anonymous]

@AlB

Thank you for your support. I will make sure to add additional details about table relationships and show some sample data in text-tablular format in the future. 

Apologies for any inconvenience. 

[Stachu · Accepted solution]

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]

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