Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

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

@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
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

Anonymous
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.