Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
M_SBS_6
Helper V
Helper V

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. 

 

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

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:
VahidDM_0-1699319171246.png

 

 

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

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

View solution in original post

1 REPLY 1
VahidDM
Super User
Super User

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:
VahidDM_0-1699319171246.png

 

 

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

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.