cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Count duplicate text values in a column

Hi all

Wondering if anyone could help me simplify my solution.

My data looks a bit like this:

I want to show, in Cycle 1 how many users chose each sector E.g.

Cycle 1: Logistics = 1

Cycle 2: Logistics = 2

I have found a solution, althought it seems very long winded as I will have up to 8 Cycles and 8+ potential sectors!

My solution is to write a measure for each Sector, in each Cycle like this:

Count Health C1 =

= CALCULATE(
COUNT(Data[C1 Sector ]),
FILTER(Data, Data[C1 Sector ] = "Health"))

Can anyone suggest a quicker/easier solution please?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Count duplicate text values in a column

Hi @Aimeeclaird ,

It is suggested to UnPivot your table first.

Then, create a measure like so:

``````Count Health Cycle =
VAR _curSector =
MAX ( Data[Value] )
RETURN
CALCULATE ( COUNT ( Data[Value] ), FILTER ( Data, Data[Value] = _curSector ) )
``````

If you want to compare two cycles value based on your selection, try to create a measure like so:

``````Compare =
VAR MaxCycle =
MAXX ( VALUES ( Data[Attribute] ), [Attribute] )
VAR MinCycle =
MINX ( VALUES ( Data[Attribute] ), [Attribute] )
RETURN
CALCULATE ( [Count Health Cycle], Data[Attribute] = MaxCycle )
/ CALCULATE ( [Count Health Cycle], Data[Attribute] = MinCycle )
``````

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Highlighted
Super User II

## Re: Count duplicate text values in a column

I see two options

``````Count Health C1 =
var _curSector = MAX(Data[C1 Sector ])
RETURN
CALCULATE(
COUNT(Data[C1 Sector ]),
FILTER(Data, Data[C1 Sector ] = _curSector )
)``````

or

``````CALCULATE(
COUNTROWS(Data),
ALLEXCEPT(Data, Data[C1 Sector ])
)``````

do not hesitate to give a kudo to useful posts and mark solutions as solution

Highlighted
Super User IV

## Re: Count duplicate text values in a column

@Aimeeclaird , Create a dimension sector .

Create a new table

Sector = distinct(union(all(Table[Cycle 1: Sector]),All(Table[Cycle 2: Sector])))

Join with both Sector, one will active another one will be inactive. Use userelation while doing count of  Cycle 1: Sector or Cycle 2: Sector, which ever is inactive

Refer to this example how to use that

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I

## Re: Count duplicate text values in a column

Thanks @az38 - this has worked well for each cycle but not if I try to compare C1 / C2 etc.

Do you have any advice for how I can compare each cycle's results in one visual please?

Apologies, I'm new!

Highlighted
Community Support

## Re: Count duplicate text values in a column

Hi @Aimeeclaird ,

It is suggested to UnPivot your table first.

Then, create a measure like so:

``````Count Health Cycle =
VAR _curSector =
MAX ( Data[Value] )
RETURN
CALCULATE ( COUNT ( Data[Value] ), FILTER ( Data, Data[Value] = _curSector ) )
``````

If you want to compare two cycles value based on your selection, try to create a measure like so:

``````Compare =
VAR MaxCycle =
MAXX ( VALUES ( Data[Attribute] ), [Attribute] )
VAR MinCycle =
MINX ( VALUES ( Data[Attribute] ), [Attribute] )
RETURN
CALCULATE ( [Count Health Cycle], Data[Attribute] = MaxCycle )
/ CALCULATE ( [Count Health Cycle], Data[Attribute] = MinCycle )
``````

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper I

## Re: Count duplicate text values in a column

@Icey Thanks so much. This worked perfectly! Thank you also for taking the time to include recorded examples and resources 🙂

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors