Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Aimeeclaird
Helper IV
Helper IV

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

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

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.

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

amitchandak
Super User
Super User

@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...

 

 

az38
Community Champion
Community Champion

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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.