Forum Discussion

telesforo1969's avatar
2 months ago
Solved

Counting Non-Blank Records

I basically need help calculating the count of records that are not-blank records within a specific period . A have a calendar table, an Id catalog table, and a fact table; they are all related.
  • danextian's avatar
    2 months ago

    Assuming, the last column still needs to be calculated, this will be better off done in Power Query as doing in DAX won't be dynamic.

    Using DAX, you will have to modify the formula for every date column added

    To count the rows  that are not zero

    CALCULATE ( COUNTROWS ( 'Table' ), KEEPFILTERS ( 'Table'[Count] <> 0 ) )
    

    You can also unpivot your data and create a measure

    To count non-zero records assuming that each id is equivalent to a single record

    non-zero records = 
    COUNTROWS ( FILTER ( VALUES ( Table02[Id] ), [id count] <> 0 ) )
    

     

    Please see the attached pbix.

  • telesforo1969's avatar
    2 months ago

    Thank you all for your responses. Thank God I found the solution I needed. I apologize if I didn't phrase my request correctly.

     

    Note: I have another level below the Id column, which is why the result is more than 1.  It's important that you study and understand the topic: context transaction