cancel
Showing results for
Search instead for
Did you mean:
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!

1 ACCEPTED SOLUTION

Accepted Solutions
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!

4 REPLIES 4
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!

Super User

## Re: Distinct Count - DAX Check Needed

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)

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

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 58 members 1,191 guests
Recent signins:
Please welcome our newest community members: