Count of transactions with more than one resolved status

[Anonymous]

I want to count the total transactions  which have more than one “resolved” status. Each transaction is associated with an unique ID.

The following is an example of data:

Id	status	date
1	open	01/02/2001
1	resolved	02/06/2001
2	open	03/03/2001
2	resolved	04/02/2001
2	reslolved	05/02/2001
3	open	01/02/2001
3	resolved	07/02/2001
3	resolved	09/02/2001
3	resolved	09/12/2001

The result should be two transaction with more than one resolved status

[v-frfei-msft]

Hi @Anonymous ,

To create a measure as below.

Measure = 
VAR k =
    FILTER ( 'Table', 'Table'[status] = "resolved" )
VAR a =
    FILTER (
        DISTINCT ( 'Table'[Id] ),
        CALCULATE ( COUNTROWS ( 'Table' ), KEEPFILTERS ( k ) ) > 1
    )
RETURN
    COUNTROWS ( a )

For more details, please check the pbix as attached.

[Anonymous]

calculate(count('Table'[status]), FILTER('Table','Table'[Id]=EARLIER('Table'[Id])), FILTER('Table','Table'[status]=EARLIER('Table'[status])))

I put your data into PowerBi (and made sure that 'resolved' was spelled correctly!!) and the above formula worked.

Thanks

Water

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Give this a try, it gives me your expected result.

Measure =
COUNTROWS (
    FILTER (
        VALUES ( YourTable[Id] ),
        CALCULATE (
            COUNTROWS ( YourTable ),
            KEEPFILTERS ( YourTable[status] = "Resolved" )
        ) > 1
    )
)

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

[mgreez]

MeasureName=

VAR OriginalTable = CALCULATETABLE ( TableName )

VAR NewColumnConCat= ADDCOLUMNS ( OriginalTable , "ConCat" , CONCATENATE(TableName[id],TableName[Status]))

VAR IDStatusCount = CALCULATE ( COUNTROWS([ConCat] , CONTAINSSTRING (status, "resolved") = TRUE))

RETURN = CALCULATE ( IDStatusCount , ">1" )