DAX count rows where count of another row (in another table) is less than 1

Question

I am fairly new to DAX. I would like to create a card in PowerBI that shows the number of categories where the count of subcategories have a value 0 based on the sum of another value.

Lets say we have a category, "region" and a subcategory "store". There can be 1 or more stores per region, but no store is in more than 1 region. Let's say some stores are open and some are closed and i give values of 1 for open and 0 for closed and have this as a column called "status". So to sum the number of open stores in each region I would sum the value of status per region. I want to create a DAX expression that will count the number of regions that have no open stores (i.e sum store status per region = 0). The regions are in table 1. The region to store mapping is in table 2 and The stores and the status column for open or closed are in table 3.

Whatever I tried, I just get the sum of all open stores.

I can get the list of region with 0 open stores in a table but I would like to make it easier to see the scale of the problem by using a card.

The data in 3 tables does not look very clear. Can you please provide sample data. — Priyanka2304

msta42a msta42a · Accepted Answer · 2020-10-09T19:16:41

If you want only a simple Value of region with no opened stores, you can create new table using SUMMARIZECOLUMNS, then you can count rows (I assumed that you have relationship between this tables):

RegionWithoutOpenedStores =
COUNTROWS (
    SUMMARIZECOLUMNS (
        'Table1'[Region],
        "NoOpenedStores", IF ( CALCULATE ( SUM ( 'Table3'[Status] ) ) > 0, BLANK (), 1)
    )
)

DAX count rows where count of another row (in another table) is less than 1

1 Answers