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

 

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

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.

Highlighted
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

 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 252 members 2,804 guests
Please welcome our newest community members: