Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
_________________________________________________________________________
Solved! Go to 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.
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
@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.
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!
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.
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 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.
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!
Reference | Other Techno |
CALx00499 | RPG, Sentinel, RDJ, APACHE, TIBCO |
CALx00889 | RPG |
CALx00882 | RPG, HTML |
CALx00056 | RPG, Shell scripts, JIRA, |
CALx01494 | RPG |
CALx01971 | RPG |
CALx01301 | NATURAL, RPG, ADABAS |
CALx02021 | Adélia, RPG, HP Alm (Application Lifecycle Management) |
CALx01415 | RPG, SAP, Outsystems |
CALx02051 | RPG |
CALx00398 | RPG |
CALx02018 | PL/SQL, RPG |
CALx00804 | RPG |
@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.
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.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |