Getting the count of common values from two filtered columns

[Anonymous]

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!

[v-juanli-msft · Accepted solution]

Hi @Anonymous

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))

Best Reagrds

Maggie

[Anonymous]

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")
            )
        )
    

[Anonymous]

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.

[Anonymous]

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] )

[AlB]

Hi @Anonymous

Are p1, p2, Permits measure? columns?

[Anonymous]

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