Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have to find with a formula or in the QueriEditor whether for every CNE (thats a ticket value in column 5 "CNE", which can repeat - there can be many rows with the same CNE (ticket) ) there is only one Enginer or Engineer ID (both would work) or multiple different ones (columns are last two in the screenshot). I would really appreciate some help, I couldn´t find anything online until now. Thanks.
Solved! Go to Solution.
Hello @Anonymous
Assuming you want the count of Engineers on each CNE this will return that.
Count = VAR RowCNE = 'Table'[CNE] RETURN CALCULATE( DISTINCTCOUNT('Table'[EngineerID]),ALL ('Table'), 'Table'[CNE] = RowCNE)
Hello @Anonymous
Assuming you want the count of Engineers on each CNE this will return that.
Count = VAR RowCNE = 'Table'[CNE] RETURN CALCULATE( DISTINCTCOUNT('Table'[EngineerID]),ALL ('Table'), 'Table'[CNE] = RowCNE)
Hi again @jdbuchanan71 ,
One last question would you know how this formula would translate in PowerQuery, I have one live source, where PowerBI tells me that direct query does not support a CALCULATED formula? Thanks in advance.
Hi @jdbuchanan71 and @RobbeVL ,
Thanks again for the help. The formula seems to be counting the number of CNE when I did the test and not whether there is 1,2,3 or 5 engineers per one CNEs. One small change, I had to use ParentCNE instead of CNE due to the specifics of one project, but that shouldn´t affect the calcualtion in any way.
https://drive.google.com/file/d/1-09inWvTdoyTMStOM5SYwmwYAuBxbhME/view?usp=sharing
https://drive.google.com/file/d/1ofmQ8JeWALpXsZ5F1IvzXExG00d0GTzd/view?usp=sharing
Also I am checking the results with the help of my colleague, my machine seems to struggle with the calculation, it never ends "working on it" 😄
Hi jdbuchanan71,
Trying it now, the syntax looks a bit strange, will let you know if it works, thanks.
@jdbuchanan71 syntax works,
You'll have to add the last part, to exclude the blank fields in your data
Count = VAR RowCNE = 'Table'[CNE] RETURN CALCULATE( DISTINCTCOUNT('Table'[EngineerName]);ALL ('Table'); 'Table'[CNE] = RowCNE; 'Table'[EngineerName] <> BLANK())
Hi @RobbeVL and @jdbuchanan71 ,
Thanks a lot again for the quick replies, seems that the formula is working 🙂 I will be testing it more and see how it goes. But for now thanks a lot.
Hi,
Could you please be more exact on what your expected output is?
You want your table to group by CNE if I'm understanding correctly?
Some sample data is always usefull too 😉
Robbe
Hi Robbe,
Thanks for the quick answer. The expected output is to see whether for every CNE (this is one of those numbers: WO-007909565) we have only one EngineerName or EngineerID or many. This can be laso by a simple True/False or SingleEngineer/ManyEngineer.
Sorry I don´t see how I can upload an excel file in my response here, see if this works
https://drive.google.com/file/d/1bOOvcXMCJNXtoWzGFF7D9k2RbVXSgJNp/view?usp=sharing
Again, only column of interest should be CNE and EngineerName/ EngineerID
This should work with a simple distinct Count measure.
Just drag CNE to rows and Engeneer to value --> click distinct count and you'll get your result.
Please mark as resolved, is this helped you with your question.
Robbe
Hi again,
I need it as a value in a calcualted column, becuase I will use it later again in another calculation or table, but thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |