Forum Discussion
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. | UserID | Cycle 1: Sector | Cycle 2: Sector |
| BA | BA 2 | Energy | Adv. Manufacturing |
| BA | BA 3 | Adv. Manufacturing | Digital |
| BA | BA 4 | Adv. Manufacturing | Education |
| BA | BA 5 | Adv. Manufacturing | Construction |
| BA | BA 6 | Digital | Energy |
| BA | BA 7 | Digital | Adv. Manufacturing |
| BA | BA 8 | Digital | Digital |
| BA | BA 9 | Digital | Education |
| BA | BA 10 | Education | Logistics |
| BA | BA 11 | Education | Construction |
| BA | BA 12 | Construction | Construction |
| BA | BA 13 | Construction | Logistics |
| BA | BA 14 | Construction | Business Admin |
| BA | BA 15 | Construction | Energy |
| BA | BA 16 | Logistics | Adv. Manufacturing |
| BA | BA 17 | Business Admin | Digital |
| BA | BA 18 | Business Admin | Education |
| BA | BA 19 | Business Admin | Construction |
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 =
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
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.
Icey Thanks so much. This worked perfectly! Thank you also for taking the time to include recorded examples and resources 🙂
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 ]) )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!
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
