Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Hi,
Try this measure:
Measure = SUMX(SUMMARIZE(VALUES(Data[WO]),Data[WO],"ABCD",DISTINCTCOUNT(Data[Unit Number])),[ABCD])
Hope this helps.
Hi,
Try this measure:
Measure = SUMX(SUMMARIZE(VALUES(Data[WO]),Data[WO],"ABCD",DISTINCTCOUNT(Data[Unit Number])),[ABCD])
Hope this helps.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
=
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:
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |