Count Distinct IDs Based on two Filter Criteria

Question

I have a table with IDs that I would like to create a measure to count the IDs&nbsp; based on two criteria.&nbsp;&nbsp;I want to count those IDs when the two criteria match&nbsp;Example&nbsp;If Criteria 1 &amp; 2 are the same, then count how many IDs that fall into that criteria.&nbsp;ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Criteria 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Criteria 21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;USA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123456782&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;USA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123456783&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;USA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12345678&nbsp;&nbsp;Answer should be 3&nbsp;Thanking you in advanceJoe

anonymous · Answer

Measure = CALCULATE( CALCULATE(COUNT(test[Column1] ),test[Column2]="usa"),test[Column3]="12345678")

anonymous · Answer

Thank you&nbsp;Anonymous&nbsp;There is multiple matching criteria in the table. Not Only USA or 12345678, there could be Canada and 23456789 or Mexico and 345678910&nbsp;The idea would be to count ids that fall into those criterias.&nbsp;Kind regardsJoe

pattemmanohar · Answer

Anonymous&nbsp;Please try this, it will work for both as a "New Column" or "Measure"&nbsp;CALCULATE(COUNTROWS(Test03Count),ALLEXCEPT(Test03Count,Test03Count[Criteria1],Test03Count[Criteria2]))

Forum Discussion

Count Distinct IDs Based on two Filter Criteria

3 Replies

Related content

DAX FILTER with multiple criteria

Dynamic Filter creation based on multiple criteria

Multiple Criteria Filter

Distinct Count If Multiple Criteria

Distinct count based on 3 criterias