Reply
Highlighted
Frequent Visitor
Posts: 3
Registered: ‎12-06-2018
Accepted Solution

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!

             

Accepted Solutions
Community Support Team
Posts: 2,269
Registered: ‎03-15-2018

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


All Replies
AlB Super Contributor
Super Contributor
Posts: 830
Registered: ‎11-12-2018

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

Hi @abrahamn

 

Are p1, p2, Permits measure? columns?

 

Frequent Visitor
Posts: 3
Registered: ‎12-06-2018

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

Established Member
Posts: 135
Registered: ‎10-26-2018

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")
            )
        )
    
Frequent Visitor
Posts: 3
Registered: ‎12-06-2018

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.

Established Member
Posts: 135
Registered: ‎10-26-2018

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
Posts: 2,269
Registered: ‎03-15-2018

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