How many times a value is repating in a column

[Anonymous]

Values

Apple

Apple

Apple

Orange

Orange

Orange

Grapes

Grapes

Grapes

Grapes

Bnana

Pear

Pear

Pear

Pear

Kiwi

Kiwi

Kiwi

Kiwi

Result I wanted via measure is: How many Times Values is repeated

Please see the image below for refrence:

[Mariusz]

Hi @Anonymous 

Please see the below.

Measure = 
VAR _duplicates = CALCULATE(
    COUNTROWS( Fruits ),
    ALL( Fruits ),
    VALUES( Fruits[Fruit] )
)
RETURN IF( _duplicates > 1, _duplicates )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz @Anonymous  Thank you for looking into this.
@Anonymous  I am working on direct query so calculated column is not working for me.

@Mariusz  I want to create chart like any of this or both of them. Could you please look into chart as per screenshot below:

[Mariusz]

Hi @Anonymous 

If you working with direct query you need to use composite models:

1. use aggregated tables to create a table with apples, bananas etc. and count on them.

2. add segmentation tables with 0 Times, 3 Time etc and create a measure to calculate.

3. add table calculated from Direct query table but don't know if this can be done.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

calculated column 1

Occurrences=
VAR thisValue=Table[Value]

RETURN
COUNTROWS(Filter(Table;Table[Values]=thisValue

Calculated column 2
Occurrences of This Value=
VAR thisOcc=Table[Occurrences]
RETURN
DISTINCTCOUNT(SELECTCOLUMNS(Filter(Table;Table[Occurrences]=thisOcc);Value))