Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Counting Rows with no records

I'm trying to create a void report that shows records that have no sales.   My issue is that some records that have no sales have no records so it will be blank.Is there a better way to count Blank...
Need Help
parry2k's avatar
parry2k
Super User
2 years ago

Anonymous try this:

 

Count Without Sales = 
VAR __TablesWithSales = 
ADDCOLUMNS ( 
       CROSSJOIN ( 
           VALUES ( 'Dim Store'[Store #], 
           VALUES ( 'Dim Product'[Nat_UPC],
           VALUES ( 'Date Table'[FW Week]
       ), 
       "@CountFact", CALCULATE ( COUNTROWS ( FactTable ) ) 
) 
RETURN COUNTX ( FILTER ( __TablesWithSales , [@CountFact] == BLANK ( ) ), 1 )