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

     

AlB's avatar
AlB
Community Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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.