Forum Discussion
Check whether values with 1 common indicator are the same (match) or are different in several rows
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.
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)
10 Replies
- RobbeVL
Impactful Individual
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 workshttps://drive.google.com/file/d/1bOOvcXMCJNXtoWzGFF7D9k2RbVXSgJNp/view?usp=sharing
Again, only column of interest should be CNE and EngineerName/ EngineerID- RobbeVL
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
- jdbuchanan71
Super User
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 jdbuchanan71,
Trying it now, the syntax looks a bit strange, will let you know if it works, thanks.- RobbeVL
jdbuchanan71 syntax works,
You'll have to add the last part, to exclude the blank fields in your dataCount = VAR RowCNE = 'Table'[CNE] RETURN CALCULATE( DISTINCTCOUNT('Table'[EngineerName]);ALL ('Table'); 'Table'[CNE] = RowCNE; 'Table'[EngineerName] <> BLANK())
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" :DHi 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.
