cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gk91 Frequent Visitor
Frequent Visitor

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.

123456789.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hello @gk91 

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 10
RobbeVL Established Member
Established Member

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

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 Smiley Wink 

 

Robbe

Super User
Super User

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

Hello @gk91 

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)
gk91 Frequent Visitor
Frequent Visitor

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

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 Established Member
Established Member

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

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.
Capture.JPG

 

Please mark as resolved, is this helped you with your question.

Robbe

gk91 Frequent Visitor
Frequent Visitor

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

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.

gk91 Frequent Visitor
Frequent Visitor

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

Hi  

RobbeVL Established Member
Established Member

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

@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())
gk91 Frequent Visitor
Frequent Visitor

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

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" Smiley Very Happy

gk91 Frequent Visitor
Frequent Visitor

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

Hi @RobbeVL  and @jdbuchanan71 ,

Thanks a lot again for the quick replies, seems that the formula is working Smiley Happy I will be testing it more and see how it goes. But for now thanks a lot.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 166 members 1,642 guests
Please welcome our newest community members: