Forum Discussion
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 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 =
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!
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 thisMeasure = COUNTROWS ( SUMMARIZE ( ZH, ZH[ZH#], ZH[ZH Ver.] ) )
4 Replies
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 thisMeasure = COUNTROWS ( SUMMARIZE ( ZH, ZH[ZH#], ZH[ZH Ver.] ) )
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!
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)
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.
