Forum Discussion

miriambb's avatar
miriambb
Helper I
4 years ago
Solved

count if

Hi everone,

 

I am a new in PowerbI so getting used to the formulas. I am familiar with Tableau, and I would like to do this calculation:

 

If April 2022 is null and. May 2022 is not null. In order to do so, I created the following calculated field in Tableau

 

count(if isnull([may2022]) THEN [June2022] END )

 

Could someone help me with the equivalent in PowerBI?

Thank you

Miriam 

Need Help
Tips and Tricks
  • themistoklis's avatar
    themistoklis
    4 years ago

    miriambb 

     

    New Measure =
    SUMX (
    TableName , -- Table Name
    IF (
    TableName [may2022] = BLANK(),
    CALCULATE(COUNT( TableName [June2022] ))
    )
    )

     

    OR 

     

    New Measure = IF (CALCULATE ( COUNT( TableName [may2022] ) ) = BLANK (), CALCULATE ( COUNT ( TableName [June2022] ) ), 0 )

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  miriambb ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated column.

    count if =
IF(
    'Table'[April 2022] = BLANK() &&'Table'[may2022]<>BLANK(),
        COUNTX(FILTER(ALL('Table'),'Table'[April 2022] = BLANK() &&'Table'[may2022]),[ID]),
IF(
    'Table'[may2022]=BLANK(),
        'Table'[June2022]))

    2. Result:

    If I have misunderstood your meaning, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. Express the results in the form of pictures, we can help you better.

     

    Best Regards,

    Liu Yang

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  miriambb ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated column.

    count if =
IF(
    'Table'[April 2022] = BLANK() &&'Table'[may2022]<>BLANK(),
        COUNTX(FILTER(ALL('Table'),'Table'[April 2022] = BLANK() &&'Table'[may2022]),[ID]),
IF(
    'Table'[may2022]=BLANK(),
        'Table'[June2022]))

    2. Result:

    If I have misunderstood your meaning, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. Express the results in the form of pictures, we can help you better.

     

    Best Regards,

    Liu Yang

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

  • themistoklis's avatar
    themistoklis
    Community Champion
    4 years ago

    miriambb 

     

    New Measure =
    SUMX (
    TableName , -- Table Name
    IF (
    TableName [may2022] = BLANK(),
    CALCULATE(COUNT( TableName [June2022] ))
    )
    )

     

    OR 

     

    New Measure = IF (CALCULATE ( COUNT( TableName [may2022] ) ) = BLANK (), CALCULATE ( COUNT ( TableName [June2022] ) ), 0 )

  • miriambb's avatar
    miriambb
    Helper I
    4 years ago

    Hi themistoklis 

     

    the first option gives me some numbers but unfortunately, it's not counting everything correctly as I am getting a different value from my previous calculation... do you know why this may be? Option 2 returns 0

     

    Thank you

    Miriam

     

     

    • themistoklis's avatar
      themistoklis
      Community Champion
      4 years ago

      miriambb 

       

      Could you share with us a sample dataset on Excel and shows us what is the expected output?

      This way we will create the right formula for you