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.
I have three table , two is dimension( DimUser, DimWeek) and one is fact table(JC), I have created a simple measure( count(JC[userWeek]) for counting attendance per week , it is returning result ok, some user are not coming in entire week ( for user Eric Kinzer he not coming week 24 and 25 ) , I need to show 0 on that week. Please see the attached.
Hi @arifulice09 ,
You can achieve that with IF statement.
Basiaclly you want to say something like this - if I receive blank result, replace it with zero.
Measure would look like this:
MyMeasure =
var _CountUsers = COUNT(JC[userWeek])
return
IF(ISBLANK(_CountUsers),0,_CountUsers))
Thanks for your reply, when I am using your measure then those user are showing in matrix who are not coming in a single day in entire month, in this case no need to showing in matrix, user like "Erica Mckenzie" , who not coming in a single day in entire month please see tha ttached.
Hi @arifulice09 ,
My Sample is as below.
JC:
DimWeek:
DimUser:
Please try this code to create a measure.
Attendance Per Week =
VAR _SUMMARIZE =
SUMMARIZE (
ALL ( DimUser ),
DimUser[User],
"Count", CALCULATE ( COUNT ( JC[userWeek] ), ALL ( DimWeek ) )
)
RETURN
IF (
COUNTAX ( FILTER ( _SUMMARIZE, [User] = MAX ( DimUser[User] ) ), [Count] )
= BLANK (),
BLANK (),
COUNT ( JC[userWeek] ) + 0
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your kind reply, I have already solved it using the help of ALL function 🙂
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 |
---|---|
108 | |
105 | |
87 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
96 | |
80 | |
72 |