Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

_________________________________________________________________________

Need Help
Show and Tell
Tips and Tricks
  • parry2k's avatar
    parry2k
    7 years ago

    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())
)

13 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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! 

       

      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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! 

       

  • tex628's avatar
    tex628
    Community Champion
    7 years ago

    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