Forum Discussion

H_insight's avatar
H_insight
Icon for Helper V rankHelper V
4 years ago
Solved

Count status by each region

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

 

  • 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")

    )

1 Reply

  • 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")

    )