topic Count row with blank data in DAX Commands and Tips

Count row with blank data

Anonymous — Mon, 19 Feb 2024 12:02:29 GMT

Hi everyone !

I have a simple request but i tried different ways but I didn't solve it.

I would like to count how many times I have a blank rows, here is the field " Groupe mandataire retraité" (the first row).

Here is the matrix :

Here, is an excel with the expected result :

I tried all those possibilities :

Measure 1 = COUNTBLANK(groupe mandataire retraité [groupe mandataire retraité])

Result : Blank (because there are blank in data?)

Measure 2 =

Result = Nothing happened

Measure 3 = IF(ISEMPTY('Groupe mandataire retraité'),COUNTA(Mandataire[Groupe mandataire]))

I don't have any solutions but maybe you have one !

The aim is also to create just a card to inform about number of blank lines.

Thank you in advance,

Re: Count row with blank data

sjoerdvn — Mon, 19 Feb 2024 12:51:41 GMT

try this:

CALCULATE(COUNTROWS('groupe mandataire retraité'),ISBLANK('groupe mandataire retraité'[groupe mandataire retraité])

Re: Count row with blank data

123abc — Mon, 19 Feb 2024 13:22:55 GMT

To count the number of blank rows based on the field "Groupe mandataire retraité" in Power BI using DAX, you can create a new calculated column or measure depending on your requirement.

Here's how you can do it:

Create a new calculated column: You can create a new calculated column using the following DAX formula:

BlankRowCount = IF(ISBLANK([Groupe mandataire retraité]), 1, 0)

This formula will assign a value of 1 to the new column if the "Groupe mandataire retraité" field is blank, and 0 otherwise.
Create a measure: If you prefer to use a measure to dynamically calculate the count of blank rows, you can create a measure using the following DAX formula:

BlankRowCount =
COUNTROWS(FILTER('YourTableName', ISBLANK('YourTableName'[Groupe mandataire retraité])))

Replace 'YourTableName' with the name of your table where the data is stored.

Once you have either the calculated column or measure in place, you can add it to your report and it will show the count of blank rows based on the "Groupe mandataire retraité" field.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Count row with blank data

Anonymous — Mon, 19 Feb 2024 17:36:10 GMT

Hi 123abc

Unfortunatly it displays blank value.

Re: Count row with blank data

Anonymous — Mon, 19 Feb 2024 17:37:09 GMT

Hi sjoerdvn ,

Unfortunatly it displays blank value 😞

Re: Count row with blank data

123abc — Tue, 20 Feb 2024 05:36:11 GMT

To count the number of blank rows based on the "Groupe mandataire retraité" column in Power BI using DAX, you can use the following approach:

Create a calculated column that checks if the "Groupe mandataire retraité" column is blank for each row.
Then, create a measure to count the number of rows where the calculated column is true.

Here are the steps to implement this:

Create a calculated column:
- Go to the table where your data is located.
- Click on "Modeling" in the menu at the top.
- Click on "New Column" in the toolbar.
- Enter the following formula for the new column:

BlankRowCheck = ISBLANK('YourTableName'[Groupe mandataire retraité])

Replace 'YourTableName' with the name of your table.
Create a measure to count blank rows:
- Click on "New Measure" in the toolbar.
- Enter the following formula for the measure:

BlankRowCount = COUNTROWS(FILTER('YourTableName', 'YourTableName'[BlankRowCheck]))

Replace 'YourTableName' with the name of your table.

After creating the measure, you can add it to a card visual to display the number of blank rows based on the "Groupe mandataire retraité" column.

This approach should give you the count of blank rows as expected in your Excel example. Make sure to replace 'YourTableName' with the actual name of your table in Power BI.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.