need to showing 0 when there is no value in data table

[arifulice09]

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.

[Migasuke]

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))

[arifulice09]

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.

[Anonymous]

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.

[arifulice09]

Thanks for your kind reply, I  have already solved it using the help of ALL function 🙂