cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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:

 

Dept.UserIDCycle 1: SectorCycle 2: Sector
BABA 2EnergyAdv. Manufacturing
BABA 3Adv. ManufacturingDigital
BABA 4Adv. ManufacturingEducation
BABA 5Adv. ManufacturingConstruction
BABA 6DigitalEnergy
BABA 7DigitalAdv. Manufacturing
BABA 8DigitalDigital
BABA 9DigitalEducation
BABA 10EducationLogistics
BABA 11EducationConstruction
BABA 12ConstructionConstruction
BABA 13ConstructionLogistics 
BABA 14ConstructionBusiness Admin
BABA 15ConstructionEnergy
BABA 16Logistics Adv. Manufacturing
BABA 17Business AdminDigital
BABA 18Business AdminEducation
BABA 19Business AdminConstruction

 

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
Community Support

Re: Count duplicate text values in a column

Hi @Aimeeclaird ,

 

It is suggested to UnPivot your table first.

 

unpivot.gif

 

Then, create a measure like so:

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

count.PNG

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 )

compare.gif

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.

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: Count duplicate text values in a column

Hi @Aimeeclaird 

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
LinkedIn

Highlighted
Super User IV
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...

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
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
Community Support

Re: Count duplicate text values in a column

Hi @Aimeeclaird ,

 

It is suggested to UnPivot your table first.

 

unpivot.gif

 

Then, create a measure like so:

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

count.PNG

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 )

compare.gif

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.

View solution in original post

Highlighted
Helper I
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 🙂

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

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!

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