Count status by each region

[H_insight]

Hi all,

I am trying to count the status by regions using the following measure, but no results are showing?

Measure = 

CALCULATE (
    COUNTROWS (
        CALCULATETABLE (
             VALUES(TableP[Region]),
            FILTER (
                TableJ,
                TableJ[Status] = "Complete" &&  TableJ[Status] = "Pending")
                
            )
        )
    )

Thought of merging two tables, but it resulted in a duplicate index (each index will have multiple rows) and PBI does not like it.

Any help with Dax and any other tips to consider for the model, please?

Sample file link.

Thanks

H

[amitchandak · Accepted solution]

@H_insight , Try like

calculate (
distinctcount(TableP[Region]),
FILTER (
TableJ,
TableJ[Status] = "Complete" && TableJ[Status] = "Pending")

)

or

calculate (
countrows(Summarize(TableP, TableP[Region],TableJ[Status])),
FILTER (
TableJ,
TableJ[Status] = "Complete" && TableJ[Status] = "Pending")

)