Forum Discussion
Helper IICount 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!
Hi,
Try this measure:
Measure = SUMX(SUMMARIZE(VALUES(Data[WO]),Data[WO],"ABCD",DISTINCTCOUNT(Data[Unit Number])),[ABCD])
Hope this helps.
8 Replies
- Ashish_Mathur
Super User
Hi,
Try this measure:
Measure = SUMX(SUMMARIZE(VALUES(Data[WO]),Data[WO],"ABCD",DISTINCTCOUNT(Data[Unit Number])),[ABCD])
Hope this helps.
WaterThank 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
- Ashish_Mathur
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.
CNENFRNLCommunity Champion
= CALCULATE( DISTINCTCOUNT( WORKLOAD[WO] ), ALLEXCEPT( WORKLOAD, WORKLOAD[YEAR] ) )- Water
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:
The output I get is:
But I was expecting something looking like:
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!
- mahoneypat
Microsoft 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
- Water
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
- newpowerbiuser8
Helper I
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.
