cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
win_toeknee Member
Member

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 =

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!

 

zhcountwversion.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Distinct Count - DAX Check Needed

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Stachu Super Contributor
Super Contributor

Re: Distinct Count - DAX Check Needed

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Super User
Super User

Re: Distinct Count - DAX Check Needed

Hi  @win_toeknee

 

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)

win_toeknee Member
Member

Re: Distinct Count - DAX Check Needed

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

 

Highlighted
win_toeknee Member
Member

Re: Distinct Count - DAX Check Needed

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 123 members 1,242 guests
Please welcome our newest community members: