Count selected rows based on filtered value

[M_SBS_6]

Hi, 

I need to count the number of distict app_no where app_status is "live" and "new".

As an example, for app_no (235) I have 2 rows of data as row one has app_status = Live and row 2 has an app_status of New. So that would count as 1 record. 

My next record app_no (236) only has one row with an app_status = Live so I wouldn't count this. 

My next record app_no (237) only has one row with an app_status = New so I wouldn't count this either. 

My final example(app_no 238) has 3 rows of data, first with app_status = New, the second row has app_status = processed and the third row has app_status = Live. Because this app has and app_status of New and Live, we'd count the app_no as 1 record. 

So out of the 4 examples, I'd return a value of 2 app_no.

App_no. App_staus

235. Live

235. New

236. Live

237. New

238. Live

238. New

238. Processed

I hope that makes sense. 

[VahidDM · Accepted solution]

Hey @M_SBS_6 

Not sure how you want to us the count output, but you can use this:

Distict app_no =

VAR _A =

    FILTER (

        SUMMARIZE (

            FILTER ( 'Table', 'Table'[App_staus] = "Live" || 'Table'[App_staus] = "New" ),

            [App_no],

            "C", COUNTROWS ( 'Table' )

        ),

        [C] > 1

    )

VAR _B =

    COUNTROWS ( _A )

RETURN

    _B



the output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube