Return Value if Countrows is 0

[azbasketcat]

I'm trying to count the number of stores with no deposits for a given day for a specific store.

I have a single table "Deposits" in PowerPivot with Store Number, Effective Date and Amount.

My pivot table will have "Store Number" in the Row Labels and "Effective Date" in the Column Labels.

My current measure is: [Stores with No Deposits] = if(countrows(Deposits)=BLANK(),1,0)

I'm missing something or thinking about the scenario where countrows returns Blank (or zero) incorrectly. Any help is appreciated.

Thanks - Randy

 

[MD610]

COUNTROWS() counts the rows of a table. It will never be equal to BLANK() because you table has rows, so it will always return a number.

You need a measure like this:

[Stores with No Deposits]:= CALCULATE(COUNTROWS(Deposits), Deposits[Amount] = BLANK())

Or like this if you do actually want to see zero's in your pivot when there is an amount:

[Stores with No Deposits 2]:= IF(SUM(Deposits[Amount]) = BLANK(), 1, 0)

 

[azbasketcat]

Mike - Thanks for your reply. I'm still having problems so let me clarify. My "Deposits" table only contains actual deposits. Therefore, if a store had no deposits on a particular day, there are no rows for that day for that store.

So essentially, I'm trying to return a result when a value or row doesn't exist. Thanks again - Randy

 

[MD610]

I see. That does make it slightly more complicated because you can't count values that don't exist. What you really need is a date table related to your deposits table. Then you want to count the rows of your date table.

Add a date table containing a single column of dates that starts with your minimum effective date and ends with your maximum effective date. Make sure it include every single date in between. Call it Dates.

Relate this new table to your effective date. Since there are multiple effective date and the date table contains the unique value for dates, PowerPivot will only let you create the relationship in one direction.

After you have the relationship created, put stores in your pivot rows and the date column from the new date table in columns.

Try this measure:

=CALCULATE(
                  COUNTROWS(Dates),
                  FILTER(ALL(Dates),
                          SUM(Deposits[Amount]) = BLANK()
                  ), 
                  Dates
  )

This uses a somewhat advanced DAX technique called cross table filtering if you want to Google it and learn more.

 

[azbasketcat]

Thank you - it worked. I'm still new to PowerPivot, so thinking through calculations in terms of columns and rows is taking me some time to figure out. Can't thank you enough for your willingness to help me out.

 

[MD610]

No problem. Help from people in forums is how I learned. The method I provided, doesn't give meaningful grand totals but it does mark the dates where stores have no deposits. You could force the correct grand totals with the use of SUMX() around the measure I provided. Many blog posts have been written on various ways of doing this.

Good luck!