Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |