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

[Mariollo]

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!

[Mariollo]

Yes, It works as expected.

Thank you.

[Bibiano_Geraldo]

Happy it worked! 😊

[Bibiano_Geraldo · Accepted solution]

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]

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]

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