Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Countrows should return where value is 0 and null where values are null

Event DateClientNPSSessionConfidence boostingPaceModerationHow satisfied are you with the content covered?
12/3/2022Client 17 Skills for tomorrow846 
13/3/2022Client 14 Skills for tomorrow793 
14/3/2022Client 16CV writing8 6 
15/3/2022Client 19CV writing7 3 
16/3/2022Client 210Digital Marketing    
17/3/2022Client 210Digital Marketing    
18/2/2023Client 27Audience developemnt    
19/2/2023Client 210Audience developemnt    
12/3/2023Client 29Marketing techniques   1
13/3/2023Client 33Marketing techniques   2
14/3/2023Client 32Marketing techniques   3
15/3/2023Client 30Marketing techniques   8
16/3/2023Client 30Marketing techniques   7

 

I have a dataset like this, I am trying to calculate the score that takes 

 

LSAT_Moderation Score demo =
 
VAR _tbl= FILTER('nps',NOT(ISBLANK('nps'[Moderation])))
VAR count_1 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==1)))
VAR count_2 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==2)))
VAR count_3 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==3)))
VAR count_4 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==4)))
VAR count_5 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==5)))
VAR count_6 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==6)))
VAR count_7 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==7)))
VAR count_8 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==8)))
VAR count_9 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==9)))
VAR count_10 = CALCULATE(COUNTROWS(FILTER(_tbl,'nps'[Moderation]==10)))
 
VAR sum_of_counts =  count_7 + count_8 + count_9 + count_10
VAR sum_of_all_counts =  count_1 + count_2 + count_3 + count_4 +count_5 +count_6 +count_7 + count_8 + count_9 + count_10
 
VAR LSAT_Score = (sum_of_counts/sum_of_all_counts)*100
RETURN LSAT_Score
 
It counts the occurrences of each of the numbers and then divides the sum.  My issue is that. In the dataset, the moderation score for 'Skills for tomorrow' should be 0 as there are no ocuurences of 7,8,9 and 10 but instead, I get 'blank', now I can add RETURN (if(ISBLANK(LSAT_Score),0,LSAT) and it does give me 0. However, for 'CV writing' we do not have the scores for Moderation and Pace so it should ignore this column from the calculation but ISBLANK(0) returns 0 for these columns as well and when I take an average of all these columns it skews the end result. How can I make sure that I get 0 where it is 0 and null where is it null?   The end result of both 'Skills for tomorrow' and 'CV writing' should be 50
Then I am using this measure to take the average only for those columns that are actually populated. 
 
LSAT_Test =
        VAR Count_ =
            INT([LSAT_Confidence Score demo] <> BLANK())
                + INT ([LSAT_Moderation Score demo] <> BLANK())
                + INT ([LSAT_Pace Score demo] <> BLANK())
                + INT ([LSAT_new] <> BLANK())
    RETURN
        DIVIDE ( [LSAT_Confidence Score demo] + [LSAT_Moderation Score demo] + [LSAT_Pace Score demo] + [LSAT_new], Count_)
1 REPLY 1
Anonymous
Not applicable

@tamerj1 Hi, this is the original dataset (sample of original dataset) while your solution of adding +0 works it would work only if we already know which column has values and which one has values. It won't work if we have multiple slicers like for client and course etc. If you could help in this one? 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.