cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abrahamn Frequent Visitor
Frequent Visitor

Getting the count of common values from two filtered columns

Capture.PNG

Hi,

I have a problem filtering two columns and finding the common among those two columns. I have two columns Process and Task as shown above. I want to take the distinct count of ID's which falls into(Application && accepted) and (Review && over).

I tried this way p1=Calculate(Distinctcount(Table(ID)),Filter'Table',Table[Process]=''Application''&& Table[Task]="accepted"))

                        P2 =Calculate(Distinctcount(Table(ID)), Filter'Table',Table[Process]="Review" && Table[Task]="over"))

                        Permits=if(Table[p1]=Table[p2], Blank(),"True")

But I don't get what I expected. From the above table , I want to get the count as 1. Is there any other way in DAX to show the count. Please help!

             
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Getting the count of common values from two filtered columns

Hi @abrahamn

From this information:

"I want to see the count of ID'S which have(Application, accepted ) and (Review,Over). In my sample data, the count of ID'S which have all these statues are ID 1. So the count is 1. That's how I want to see it."

 

I make a test as below

Create measures

Applicationaccepted_count = COUNTROWS(FILTER(ALLEXCEPT(Sheet3,Sheet3[ID]),Sheet3[merge]="Applicationaccepted"))

Reviewover_count = COUNTROWS(FILTER(ALLEXCEPT(Sheet3,Sheet3[ID]),Sheet3[merge]="Reviewover"))

distinct_count = CALCULATE(DISTINCTCOUNT(Sheet3[ID]),FILTER(ALL(Sheet3),[Applicationaccepted_count]>0&&[Reviewover_count]>0))

10.png

Best Reagrds

Maggie

 

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Getting the count of common values from two filtered columns

Hi @abrahamn

 

Are p1, p2, Permits measure? columns?

 

abrahamn Frequent Visitor
Frequent Visitor

Re: Getting the count of common values from two filtered columns

P1 and P2 are the the count of ID's, they are measure to get the count. I want to get the count of ID's which are common among those P1 and P2

Highlighted
Super User
Super User

Re: Getting the count of common values from two filtered columns

From the sample data it looks like it should  be a count of 2 for each, not sure about the 1?

 

That's what Im getting using these two measures:

Application Accepted = 
    CALCULATE(
        DISTINCTCOUNT(Table1[ID ] ),
        FILTER(
            Table1,
                AND( Table1[Process] ="Application", Table1[Task] ="Accepted")
        )
    )


Review Over = 
    CALCULATE(
        DISTINCTCOUNT(Table1[ID ] ),
        FILTER(
            Table1,
                AND( Table1[Process] ="Review", Table1[Task] ="Over")
            )
        )
    
abrahamn Frequent Visitor
Frequent Visitor

Re: Getting the count of common values from two filtered columns

I want to see the count of ID'S which have(Application, accepted ) and (Review,Over). In my sample data, the count of ID'S which have all these statues are ID 1. So the count is 1. That's how I want to see it.

Super User
Super User

Re: Getting the count of common values from two filtered columns

This should be what you are looking for.  Will give you a new table, then can do whatever counts off of that

New Table = 
Var ApplicationAccepted = SELECTCOLUMNS( FILTER( Table1, AND( Table1[Process] ="Application", Table1[Task] ="Accepted") ), "ID", Table1[ID] ) RETURN Var ReviewOver = SELECTCOLUMNS( FILTER( Table1, AND( Table1[Process] ="Review", Table1[Task] ="Over") ), "ID", Table1[ID] ) RETURN INTERSECT( ApplicationAccepted, ReviewOver)
Measure = DISTINCTCOUNT(New Table[ID] )
Community Support Team
Community Support Team

Re: Getting the count of common values from two filtered columns

Hi @abrahamn

From this information:

"I want to see the count of ID'S which have(Application, accepted ) and (Review,Over). In my sample data, the count of ID'S which have all these statues are ID 1. So the count is 1. That's how I want to see it."

 

I make a test as below

Create measures

Applicationaccepted_count = COUNTROWS(FILTER(ALLEXCEPT(Sheet3,Sheet3[ID]),Sheet3[merge]="Applicationaccepted"))

Reviewover_count = COUNTROWS(FILTER(ALLEXCEPT(Sheet3,Sheet3[ID]),Sheet3[merge]="Reviewover"))

distinct_count = CALCULATE(DISTINCTCOUNT(Sheet3[ID]),FILTER(ALL(Sheet3),[Applicationaccepted_count]>0&&[Reviewover_count]>0))

10.png

Best Reagrds

Maggie

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 495 members 4,439 guests
Please welcome our newest community members: