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
Water
Helper I
Helper I

Count based on distinct values in another column of same table – DAX for measure?

Hello,

New to Power Bi, so pardon my probably novice (and first!) question.

Below is my simplified dataset. I want to create a table counting the number of units worked on in a year as per distinct work orders (WOs) . Each work order represents “one work” and can have one or more jobs.

 

Year

WO Number

Job

Unit Number

2019

101

A

1

2019

101

B

1

2019

102

A

2

2019

103

A

1

2020

104

A

2

2020

104

B

2

2020

105

A

1

2020

106

A

2

2020

107

A

1

 

I am hoping for this output:

 

Year

Total No of Units worked on

2019

3

2020

4

 

So not the number of distinct units worked on in a year - which would have been 2 and 2 for 2019, 2020.

Also not just counting the Unit Numbers (= rows) , as this would yield an incorrect 4 and 5 for 2019, 2020.

 

I presume I would need a measure with DAX counting the number of Unit Numbers based on distinct values of work order numbers. Somehow I just can’t find this exact solution by googling/searching. Please help!

Thanks!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try this measure:

Measure = SUMX(SUMMARIZE(VALUES(Data[WO]),Data[WO],"ABCD",DISTINCTCOUNT(Data[Unit Number])),[ABCD])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Try this measure:

Measure = SUMX(SUMMARIZE(VALUES(Data[WO]),Data[WO],"ABCD",DISTINCTCOUNT(Data[Unit Number])),[ABCD])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you very much, Ashish! Works great!

 

Would you mind to explain to me the logic around the "ABCD"? Is it a new table you create in which you summarize the WOs, and the you count the distinct Unit Numbers from this ABCD table?

 

Kind regards, 

W

You are welcome.  ABCD is not a table.  It is the title of the second column of table.  Under this column will be the values of distinctcount. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Employee
Employee

Looks like you just need = DISTINCTCOUNT(Workorders[WO_NO])

 

Have you tried that?  Also, do you have a separate Date table?  If so, make sure that both columns in the relationship between your Date and Workorders table are the same (type Date).

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Capture111.PNG

 

Hello! 

 

I want to calculate distinct values of column A. Which i did by using the distinct function in a measure I created. 

 

Now I want to calculate the total number of "No" for each unique value only. Can anyone please help? @mahoneypat 

 

For example the total should be 3 for "No" as I want to count for Saturday as only 1 not 2 times. 

Thank you very much, Pat!

 

It indeed works as each distinct WO represents the unit worked on.

 

Since I do need this logic thought (count based on distinct values in another column of same table), I was just wondering if there would be a way to do that in DAX?

 

(And no, no seperate Date table. Seems some users prefer having a seperate Date table as best practice. Something I must still look into.)

 

Sincerely, 

 

W

CNENFRNL
Community Champion
Community Champion

=
CALCULATE(
    DISTINCTCOUNT( WORKLOAD[WO] ),
    ALLEXCEPT( WORKLOAD, WORKLOAD[YEAR] )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Thank you! Appreciate your time.

 

But seems not to be working. In the first place I am exactly not sure of your logic, as I see the reference to distinct WO count, but no link to Unit Number count?

 

Below my measure based on you suggestion:

 

Water_0-1628351666705.png

The output I get is:

Water_1-1628351912575.png

But I was expecting something looking like:

 

Water_2-1628351964079.png

 

Also, when completing the Matrix Visualization with the above Measure 2, it doesn't show "Count of" in the Value field. The Format for Measure 2 is Whole Number.

 

Just to add: the Unit Numbers are the unique identifiers of each unit being worked on. So Unit 1 was worked on twice in 2019 (with WOs 101 & 103), and twice in 2020 (with WOs 105 & 107). Unit 2 was worked on once in 2019, and twice in 2020.

 

What would you suggest I try next?

 

Thanks!

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.