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.
Please help me to find correct answer:
Fact Table: FactStudent
SchoolId | StudId | InSession | |
S1 | 11 | 5 | SchoolLevelP= DISTINCT COUNT(StudId (NOTE: Those are in insession 7,8,9) / TotalStudID(DISTINCT)*100 |
S1 | 12 | 6 | |
S1 | 13 | 4 | |
S1 | 14 | 8 |
|
S1 | 15 | 7 |
|
S1 | 16 | 9 |
|
S1 | 17 | 8 |
|
S1 | 18 | 0 | Status = FILTER ( |
S1 | 19 | 9 | |
S1 | 20 | 8 | |
S1 | 21 | 9 | |
S1 | 22 | 9 | |
S1 | 23 | 9 | |
S1 | 24 | 7 | |
S1 | 25 | 3 |
|
S2 | 111 | 8 |
|
S2 | 112 | 9 |
|
S2 | 113 | 4 |
|
S2 | 114 | 4 |
|
S2 | 115 | 8 |
|
S2 | 116 | 7 |
|
S2 | 117 | 7 |
|
S2 | 118 | 9 |
|
S2 | 119 | 9 |
|
S2 | 120 | 8 |
|
I have created measure below and there is a configuration table for minimum/maximum range along with status column (performance labels). to show label wise school count. Ex:
Table: Configuration
Status | MaxValue | MinValue |
|
Excellent | 100 | 70 |
|
Average | 70 | 50 |
|
OK | 50 | 0 |
|
Expected result: (There are 50 schools in fact table)
Status | School Count |
Excellent | 10 |
Average | 35 |
OK | 5 |
Below I tried to achieve by calculated measure.
Measure1 :=
VAR RS =
CALCULATE (
COUNT ( FactStudent [Schoolid] ),
FILTER (
FactStudent,
AND (
FactStudent [InSession] >= 7,
FactStudent [InSession] <= 9
)
)
)
VAR SS =
COUNT ( FactStudent[StudId] )
RETURN
CALCULATE (
DISTINCTCOUNT ( FactStudent[SchoolId] ),
FILTER (
ADDCOLUMNS (
DimSchool,
"SchoolLevelP", CALCULATE (
DIVIDE ( RS, SS, 0 ) * 100,
CALCULATETABLE (FactStudent)
)
),
COUNTROWS (
FILTER (
Configuration,
[SchoolLevelP] >= Configuration[MinValue]
&& [SchoolLevelP] < Configuration[MaxValue]
)
)
> 0
),
FILTER (
FactStudent,
FactStudent[SchoolId] = FactStudent[SchoolId]
)
)
Solved! Go to Solution.
hi, @Tejesh_Gour
You may try this formula
Measure2 = CALCULATE ( DISTINCTCOUNT ( FactStudent[SchoolId] ), FILTER ( ADDCOLUMNS ( DimSchool, "SchoolLevelP", CALCULATE ( DIVIDE ( CALCULATE ( COUNT ( FactStudent[Schoolid] ), FILTER ( FactStudent, AND ( FactStudent[InSession] >= 7, FactStudent[InSession] <= 9 ) ) ), COUNT ( FactStudent[StudId] ) ) * 100 ) ), COUNTROWS ( FILTER ( Configuration, [SchoolLevelP] >= Configuration[MinValue] && [SchoolLevelP] < Configuration[MaxValue] ) ) > 0 ) )
Result:
Best Regards,
Lin
hi, @Tejesh_Gour
You may try this formula
Measure2 = CALCULATE ( DISTINCTCOUNT ( FactStudent[SchoolId] ), FILTER ( ADDCOLUMNS ( DimSchool, "SchoolLevelP", CALCULATE ( DIVIDE ( CALCULATE ( COUNT ( FactStudent[Schoolid] ), FILTER ( FactStudent, AND ( FactStudent[InSession] >= 7, FactStudent[InSession] <= 9 ) ) ), COUNT ( FactStudent[StudId] ) ) * 100 ) ), COUNTROWS ( FILTER ( Configuration, [SchoolLevelP] >= Configuration[MinValue] && [SchoolLevelP] < Configuration[MaxValue] ) ) > 0 ) )
Result:
Best Regards,
Lin
Thanks! Its working as expacted result.
hi, @Tejesh_Gour
It's pleasant that your problem has been solved, could you please mark my reply as Answered?
Best Regards,
Lin
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |