Forum Discussion
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.
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.
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
6 Replies
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.
Hi telesforo1969 ,
You can use the DAX function COUNTA to get the count of non blank rows.
Hi,
This DAX pattern should work
=if(hasonefilter(Calendar[date]),sum(Data[Amount]),countrows(Data))
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
Hi telesforo1969,
Thank you danextian Ashish_Mathur djurecic for your responses to the query.
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.
Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!Hi telesforo1969,
Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help.
Thank you.
