Forum Discussion

whitefomalhaut's avatar
whitefomalhaut
Regular Visitor
8 years ago
Solved

Only dublicates count

Hi, Im new in Power BI, so I hope someone will help,I need measure that count only values of dublicates without counting of other data in one Column that counts more than once

a
a
a
b
c
d
f
g
h
b

 

I need count 2 (only aaa and bb) how is it possible?

 

  • McCow's avatar
    McCow
    8 years ago

    Yes, whitefomalhaut

    But this solution can be simple realise with calculated column. With measure is IMHO a little complex.

    Look: 

     

    Calculated_Column = IF(CALCULATE(COUNTROWS(Table1);ALLEXCEPT(Table1;Table1[Data]))>1;
    	1/CALCULATE(COUNTROWS(Table1);ALLEXCEPT(Table1;Table1[Data]));BLANK())

    The new EXAMPLE code is here to

    Best regs

5 Replies

  • McCow's avatar
    McCow
    Resolver III
    8 years ago

    Hi whitefomalhaut!

     

    You need a measure like this:

    Measure = 
VAR __Count = CALCULATE(COUNTROWS(Table1);ALLEXCEPT(Table1;Table1[Data]))
RETURN
IF(__Count >1;__Count;BLANK())

    Example code is HERE

     

    The white table is yours source data, the yellow is a requested result. Indeed?

     

    Multiple values count only

    Here is the trick for correct visualisation (thx @Vvelarde)


    Best regards

    • whitefomalhaut's avatar
      whitefomalhaut
      Regular Visitor
      8 years ago

      Hi, ty for your answer, is it possible to count every aaa and bb as 1 not 2 and 3 in total i need count as 2 

      • McCow's avatar
        McCow
        Resolver III
        8 years ago

        Yes, whitefomalhaut

        But this solution can be simple realise with calculated column. With measure is IMHO a little complex.

        Look: 

         

        Calculated_Column = IF(CALCULATE(COUNTROWS(Table1);ALLEXCEPT(Table1;Table1[Data]))>1;
        	1/CALCULATE(COUNTROWS(Table1);ALLEXCEPT(Table1;Table1[Data]));BLANK())

        The new EXAMPLE code is here to

        Best regs