Forum Discussion
Count selected rows based on filtered value
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.
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
1 Reply
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