Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Count row with blank data

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, 

 

DAX
  • 123abc's avatar
    123abc
    2 years ago

    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:

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

    Here are the steps to implement this:

    1. 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é])

     

    1. Replace 'YourTableName' with the name of your table.

    2. 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]))

     

    1. 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.

5 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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:

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

    Here are the steps to implement this:

    1. 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é])

     

    1. Replace 'YourTableName' with the name of your table.

    2. 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]))

     

    1. 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.

  • sjoerdvn's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    2 years ago

    try this:

    CALCULATE(COUNTROWS('groupe mandataire retraité'),ISBLANK('groupe mandataire retraité'[groupe mandataire retraité])
  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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:

    1. 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)

     

    1. This formula will assign a value of 1 to the new column if the "Groupe mandataire retraité" field is blank, and 0 otherwise.

    2. 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é])))

     

    1. 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi 123abc  

    Unfortunatly it displays blank value.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi sjoerdvn , 

    Unfortunatly it displays blank value 😞