Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

HELP

I have a measure below but I dont want to calculate blanks as 0 

 

HTPTS = IF ( 'BSCA Attendance'[HT1] = 0, 100, 0 ) + IF('BSCA Attendance'[HT2]=0,100,0) + IF('BSCA Attendance'[HT3]=0,100,0) + IF('BSCA Attendance'[HT4]=0,100,0) + IF('BSCA Attendance'[HT5]=0,100,0) + IF('BSCA Attendance'[HT6]=0,100,0)
 
its calculating the blanks and giving the value of 100 for each blank so 600 in total if all 0 or blank
 
Any ideas how to fix this 
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi Anonymous ,

     

    According to your description, for row is blank(), its default type is Text and cannot be judged directly, you need to use the isblank function. I made a modification, the reference is as follows:

     

    c = 
IF ( 'BSCA Attendance'[HT1] = 0, 100, 0 )
    + IF ( 'BSCA Attendance'[HT2] = 0, 100, 0 )
    + IF ( ISBLANK ( 'BSCA Attendance'[HT3] ), 100, 0 )
    + IF ( 'BSCA Attendance'[HT4] = 0, 100, 0 )
    + IF ( 'BSCA Attendance'[HT5] = 0, 100, 0 )
    + IF ( ISBLANK ( 'BSCA Attendance'[HT6] ), 100, 0 )

     


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    Add the following code as a new column to get the desired results.

    HTPTS = 
COUNTROWS(
    FILTER(
        { 'BSCA Attendance'[HT1] ,'BSCA Attendance'[HT2] , 'BSCA Attendance'[HT3], 'BSCA Attendance'[HT4], 'BSCA Attendance'[HT5], 'BSCA Attendance'[HT6] },
        NOT ([Value] == BLANK()) && [Value]=0 
    )
) * 100
  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi Anonymous ,

     

    According to your description, for row is blank(), its default type is Text and cannot be judged directly, you need to use the isblank function. I made a modification, the reference is as follows:

     

    c = 
IF ( 'BSCA Attendance'[HT1] = 0, 100, 0 )
    + IF ( 'BSCA Attendance'[HT2] = 0, 100, 0 )
    + IF ( ISBLANK ( 'BSCA Attendance'[HT3] ), 100, 0 )
    + IF ( 'BSCA Attendance'[HT4] = 0, 100, 0 )
    + IF ( 'BSCA Attendance'[HT5] = 0, 100, 0 )
    + IF ( ISBLANK ( 'BSCA Attendance'[HT6] ), 100, 0 )

     


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.