Grillo9 avatar image
Grillo9 asked

Problem w/Powerpivot Dax Countrows Function

I have a powerpivot table that lists sales data for stores. The main driver is the YearMonth which is a varchar(6) field. This is also a slicer along with state, StoreType (Dealer or Corporation), StoreCycle, StoreWarehouse (where store delivers from). While selecting a YearMonth on the slicer (ie. 201212), the StoreCycle (ie. 11) and the StoreType (ie. Dealer) I count the CurrYrNbrStores (Current Year number of stores open) using the following measure: =sum('StoreData'[StoreOpen]) where StoreOpen is an integer with value of 1 if the store was open or 0 if it was closed. The calculation comes out correct for all of the slicers I select. My problem is when trying to calculate the PrevYrNbrStores (previous year number of stores) while sitting on the current year YearMonth slicer. From my example I need to go to 201112 and count how many 1 I have on the StoreOpen column considering the slicers currently selected. To accomplish this I use the countrows Dax measure as follows: =COUNTROWS(FILTER(StoreData, (DISTINCT(StoreData[YearMonth]) = DISTINCT(StoreData[YM12Back]) && StoreData[StoreOpen]=1))) ..where YM12Back is a column that has the same month for the previous year, in my example 201112. The result comes back blank. If I use >= on this measure (just to see if I get a number) it would give me the CurrYrNbrStores. I have the StoreData table linked to other tables; Cycle by CycleNbr, Store by StoreNbr and YearMonth by the YrMonth. I wonder if this links are preventing me from getting another set of data (filtering) for a previous year using the countrows Dax function. I've also used SUMX, COUNTX, COUNTAX functions with no luck. Thanks in advance for your help.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.