Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Distinct Count

Hi Everyone, 

 

I would like to request your support on the following measure.

I need to distinctcount all #Refs that have both Angular and .NET, however, some of those #Refs have both technologies (which translates in 2 lines) and they are counted for both technologies. Can you help me find a logic to distinct count #Refs and also only count 1 even if they have both technologies. See below. Thank you.

_________________________________________________________________________
M_Count_Tech_Angular&.NET =                                                           
VAR ANG = CALCULATE(DISTINCTCOUNT(Sourcing[Reference]);        
FILTER(Sourcing;Sourcing[Tech] = "Angular");
FILTER(Sourcing; Sourcing[Table1] = "Test");
FILTER(Sourcing; Sourcing[ATT Level] <> BLANK())
)

VAR NET = CALCULATE(DISTINCTCOUNT(Sourcing[Reference]);
FILTER(Sourcing;Sourcing[Tech] = ".NET");
FILTER(Sourcing; Sourcing[Table1] = "Test");
FILTER(Sourcing; Sourcing[ATT Level] <> BLANK())
)
RETURN
ANG+NET

_________________________________________________________________________

Capture.JPG

1 ACCEPTED SOLUTION

@Anonymous change your measure 

 

M_Count_Tech_Angular&.NET =                                                           
CALCULATE(DISTINCTCOUNT(Sourcing[Reference]);        
FILTER(Sourcing;Sourcing[Tech]  IN {"Angular", ".NET"});
FILTER(Sourcing; Sourcing[Table1] = "Test");
FILTER(Sourcing; Sourcing[ATT Level] <> BLANK())
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

13 REPLIES 13
tex628
Community Champion
Community Champion

I would advice you to create a calculated column, to classify if the reference appears in one ore more techs. 
It should output: 
.NET
Angular 
Both
This should mean a distinct count on .NET/Angular should return the references that only have a single tech. Adding the distinct count of Both should give you the total without any duplicates. 

Hope this helps ! 

Br,
J


Connect on LinkedIn
parry2k
Super User
Super User

@Anonymous if they are in both technologies, which technology they will be counted against, angular or net



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi @parry2k 

These #Refs are resources, which means that if one engineer has both technologies I only want to count him once. 

What is happening now is that I have 1 person that has both, RPG and Adelia, and this person is being counted 2x.

Is it clear now? 

 

Thank you for your help! 

 

Anonymous
Not applicable

Hi @parry2k ,

They only need to count once as they only need one to be in the bucket. These Refs are Resources. Which means that if you have 1 person with both technologies, you want to count this person only once. 

Is it clear now? Thank you for your help! 

 

@Anonymous nope, it didn't answered my question. I understand you want to count the person once, but in your pie chart, under which technology that person will get counted toward?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Oh sorry, 

In the green slice. In the example I have Angular and .NET, and I just wrote Adelia and RPG above. But I have multiple pie charts like this one to show technologies against all distinct count #REFS.

 

Does that make sense now ?

Thank you very much. 

@Anonymous change your measure 

 

M_Count_Tech_Angular&.NET =                                                           
CALCULATE(DISTINCTCOUNT(Sourcing[Reference]);        
FILTER(Sourcing;Sourcing[Tech]  IN {"Angular", ".NET"});
FILTER(Sourcing; Sourcing[Table1] = "Test");
FILTER(Sourcing; Sourcing[ATT Level] <> BLANK())
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k 

It did not work, counts 0. 

 

What could it be ?

@Anonymous cannot tell why without looking at data. if you can share data using excel and remove any sensitive inforamtion, it will help



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

As you can see there is one Reference that contain both technologies. 

This is directly extracted from the excel. "Other Techno" column has been shaped in Power BI, so I have one line per technology in my data model. Makes Sense?

Thank you! 

 

ReferenceOther Techno
CALx00499RPG, Sentinel, RDJ, APACHE, TIBCO
CALx00889RPG
CALx00882RPG, HTML
CALx00056RPG, Shell scripts, JIRA,
CALx01494RPG
CALx01971RPG
CALx01301NATURAL, RPG, ADABAS
CALx02021Adélia, RPG, HP Alm (Application Lifecycle Management)
CALx01415RPG, SAP, Outsystems
CALx02051RPG
CALx00398RPG
CALx02018PL/SQL, RPG
CALx00804RPG

@Anonymous can you share how your data is in powerbi, i don't want o redo the work you alredy did.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Sorry, works like a charm now ! Apologies, I was just missing something in one of my filters! Your solution worked.

Cheers!

Kind Regards,

Nelson 

@Anonymous awesome good to hear. cheers!!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.