What is the trigger for No in the data source?

[bppurnell]

Hello Good People.  My team had a Power BI developer recently make some enhancements to our Power BI dashboard but once I updated the data source, the DAX doesn't seem to be triggering properly.  Simply put, out of a quarterly survey the equation is suppose to divide the completed surveys by total number of participants.  The dashboard is properly displaying the total completed surveys versus total participants but the calculation is returning 100% as the response as if all surveys were completed, which they weren't.  So in my data source, I'm trying to determine what the trigger is for the DAX formula to count the "N's" of participants who did not complete the survey for the equation to return the proper response rate.  The formula is below and the "Quarter", "Partner" nor "Year" can be blank to ensure the survey responses are aligned properly. Thanks in advance for any assistance that can be provided. 

Response Rate_survey =

var yr= SELECTEDVALUE('SPP General Key Stakeholder Survey'[Year])

vAR qtr= SELECTEdVALUE('SPP General Key Stakeholder Survey'[Quarters])

var tower=selectedvalue('SPP General Key Stakeholder Survey'[Tower])

var b= IF(AND(ISBLANK(yr),AND(ISBLANK(qtr),ISBLANK(tower))),"all blank",if(AND(ISBLANK(yr),ISBLANK(qtr)),"Year and Qtr blank",IF(AND(ISBLANK(yr),ISBLANK(tower)),"Year and Tower blank",IF(AND(isblank(qtr),ISBLANK(tower)),"Quarter and Tower Blank",if(ISBLANK(yr),"year blank",if(ISBLANK(qtr),"quarter blank",IF(ISBLANK(tower),"tower blank","Nothing Blank")))))))

var Yes= IF(b="all blank", CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[participation]="Y"),IF(b="year blank", CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[participation]="Y",'SPP Key Stakeholder Satisfaction'[Quarters]=qtr, 'SPP Key Stakeholder Satisfaction'[Tower] in {tower}),IF(b="quarter blank",CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[participation]="Y",'SPP Key Stakeholder Satisfaction'[Year]=yr,'SPP Key Stakeholder Satisfaction'[Tower] in {tower}), IF(b="Nothing Blank",CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[participation]="Y",'SPP Key Stakeholder Satisfaction'[Year]=yr,'SPP Key Stakeholder Satisfaction'[Quarters]=qtr, 'SPP Key Stakeholder Satisfaction'[Tower] in {tower}),IF(b="tower blank",Calculate(count('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[participation]="Y",'SPP Key Stakeholder Satisfaction'[Year]=yr,'SPP Key Stakeholder Satisfaction'[Quarters]=qtr),IF(b="Year and Tower blank", Calculate(count('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="Y", 'SPP Key Stakeholder Satisfaction'[Quarters]=qtr),If(b="Quarter and Tower Blank",Calculate(Count('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="Y",'SPP Key Stakeholder Satisfaction'[Year]=yr),IF(b="Year and Qtr blank",CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="Y",'SPP Key Stakeholder Satisfaction'[Tower]=tower)))))))))

var No= IF(b="all blank", CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="N"),IF(b="year blank", CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="N",'SPP Key Stakeholder Satisfaction'[Quarters]=qtr, 'SPP Key Stakeholder Satisfaction'[Tower] in {tower}),IF(b="quarter blank",CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="N",'SPP Key Stakeholder Satisfaction'[Year]=yr,'SPP Key Stakeholder Satisfaction'[Tower] in {tower}), IF(b="Nothing Blank",CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="N",'SPP Key Stakeholder Satisfaction'[Year]=yr,'SPP Key Stakeholder Satisfaction'[Quarters]=qtr, 'SPP Key Stakeholder Satisfaction'[Tower] in {tower}),IF(b="tower blank",Calculate(count('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="N",'SPP Key Stakeholder Satisfaction'[Year]=yr,'SPP Key Stakeholder Satisfaction'[Quarters]=qtr),IF(b="Year and Tower blank", Calculate(count('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="N", 'SPP Key Stakeholder Satisfaction'[Quarters]=qtr),If(b="Quarter and Tower Blank",Calculate(Count('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="N",'SPP Key Stakeholder Satisfaction'[Year]=yr),IF(b="Year and Qtr blank",CALCULATE(COUNT('SPP Key Stakeholder Satisfaction'[Name]),'SPP Key Stakeholder Satisfaction'[Participation]="N",'SPP Key Stakeholder Satisfaction'[Tower]=tower)))))))))

var response=DIVIDE(Yes,(Yes+No))

return response

@dax @developer @Dashboard 

[v-xiaotang]

Hi @bppurnell 

Thanks for reaching out to us.

Does this dashboard work fine before you update the datasource? If it was working fine before, check what has been updated, this will help in locating the problem. Also, did the formula report an error after the update, or did it just not work as it did before?

Best Regards,

Community Support Team _Tang

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

[bppurnell]

There are no errors and the dashboard worked fine prior to the data source beeing changed.  As a part of an enhancement project, two data sources were combined into one.  As a result, I can't figure out the trigger to recognize the "N's" in the data to properly calcualte the participation rate.