Check whether values with 1 common indicator are the same (match) or are different in several rows

[Anonymous]

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.

[jdbuchanan71 · Accepted 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)

[Anonymous]

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.

[Anonymous]

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" 😄

[Anonymous]

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 data

Count = 
VAR RowCNE = 'Table'[CNE]
RETURN 
CALCULATE(
DISTINCTCOUNT('Table'[EngineerName]);ALL ('Table'); 'Table'[CNE] = RowCNE; 'Table'[EngineerName] <> BLANK())

[Anonymous]

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.

[RobbeVL]

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

[Anonymous]

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

[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

[Anonymous]

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.