Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 =
Solved! Go to Solution.
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.
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 🙂
@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
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |