Forum Discussion

Mughees's avatar
Mughees
Helper II
5 years ago
Solved

Filtering blank rows through power query before applying IF command

Hi All,

 

I am making a dashboard that has got calculations. My scenario is this:

 

I have created a data set that has got different indicators and it is being represented for different hierarchies. Below is the matrix that has KPI's for your reference.

 

One of the indicators NPAFP rate has different denominator in calculation for Division, District and Tehsil. That is why I plan to create three different columns for NPAFP (two are already there). NPAFP rate formula is below:

 

NPAFP rate District = if (AFP[m.YRONSET] <= VALUE("2018"),(COUNT(AFP[District]))/MAXX(AFP,AFP[Population target 2017-18])*100000*12/((month(today())-.5)),(COUNT(AFP[District]))/MAXX(AFP,AFP[Population Target 2018-19])*100000*12/((month(today())-.5)))
 
Due to difference in population, its formula get different for Division, district and Tehsil level. Since Division is the biggest and it contains districts and than districts contain Tehsils.
 
The issue I am facing is that my data base have some empty values for Tehsil, however, district and Division are always there. Hence, I want to add something in the above query that only takes rows of Tehsils that are filled. I cannot apply such filter through slicer. If apply it in slicer, many columns that have districts in their value get ommited. 
 
Kindly guide me so that I can get it fixed. 
 
Regards
Need Help
  • Payeras_BI's avatar
    Payeras_BI
    5 years ago

    Hi Mughees ,

     

    If your issue is with that COUNT in the numerator taking the blanks into account for the Tehsil measure, you could try this:

     

    COUNT () - COUNTBLANK()

     

    See below:

     

    Sample Table:

     

     

    Sample Matrix:

     

    Regards,

     

2 Replies

  • Payeras_BI's avatar
    Payeras_BI
    Solution Sage
    5 years ago

    Hi Mughees ,

     

    If your issue is with that COUNT in the numerator taking the blanks into account for the Tehsil measure, you could try this:

     

    COUNT () - COUNTBLANK()

     

    See below:

     

    Sample Table:

     

     

    Sample Matrix:

     

    Regards,

     

    • Mughees's avatar
      Mughees
      Helper II
      5 years ago

      Thankyou. BTW i just applied counta and it resolved my issue preety quickly. thank you though