Forum Discussion

Mariollo's avatar
Mariollo
Helper I
1 year ago
Solved

How to Count Values From Column Where Values In Other Column Are Empty

Hello everyone,

I am working on a Power BI report and need some help creating a DAX measure. Here's the scenario:

I have a dataset with the following columns:

  • COUNTRY
  • CITY
  • ATTRIBUTE
  • PRODUCT

I want to calculate the number of unique cities where all rows in the PRODUCT column are empty. If a city has even one non-empty PRODUCT, it should be excluded from the count.

For example, in the dataset below:

 

 

The result should be 1, because only LONDON satisfies the condition that all PRODUCT rows are empty, and LONDON is counted once as a unique city.

Could someone help me write a DAX measure to achieve this?

Thank you!

Need Help
Show and Tell
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi Mariollo ,

    You can achieve your goal by this measure:

    UniqueEmptyProductCities = 
CALCULATE(
    COUNTROWS(
        FILTER(
            VALUES('Table'[CITY]),
            CALCULATE(
                COUNTROWS('Table'),
                NOT(ISBLANK('Table'[PRODUCT]))
            ) = 0
        )
    )
)

     

    As you can see bellow, in the card, the result is 1 and in the table the result was LONDON:

     

5 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi Mariollo ,

    You can achieve your goal by this measure:

    UniqueEmptyProductCities = 
CALCULATE(
    COUNTROWS(
        FILTER(
            VALUES('Table'[CITY]),
            CALCULATE(
                COUNTROWS('Table'),
                NOT(ISBLANK('Table'[PRODUCT]))
            ) = 0
        )
    )
)

     

    As you can see bellow, in the card, the result is 1 and in the table the result was LONDON:

     

    • Mariollo's avatar
      Mariollo
      Helper I
      1 year ago

      This one works, but I simplified too much my data.

      What if I have many other columns with attributes for each product?

       

      (Updated first post)

      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Super User
        1 year ago

        It will work, because we're using only the column of city and product to reach our goal.

         

        try out and give me a feedback.

         

        Thank you