Result based on rows against 1 unique value

[TSK]

I have Order and Name column.. I want to check If against one unique Order  "Name" contains "Ali" and "Saqib" It should return 1 in result coulmn otherwise 0, Just like in followwing example in Order Column only B has "Ali" and  "Saqib" so result should be 1 only for B. How to achieve this?

Order	Name	Result
A	Ali	0
A	Muhammad	0
A	Ashraf	0
B	Ali	1
B	Saqib	1
C	Ali	0

[Anonymous · Accepted solution]

@TSK 

Try this DAX:

Result = 
var Ali = CALCULATE(COUNTROWS(Sheet1),FILTER(ALLEXCEPT(Sheet1,Sheet1[Order]),Sheet1[Name]="ali"))
var Saqib = CALCULATE(COUNTROWS(Sheet1),FILTER(ALLEXCEPT(Sheet1,Sheet1[Order]),Sheet1[Name]="saqib"))
Return IF(Ali>=1 && Saqib>=1,1,0)

 

Best Regards

Paul Zheng

[TSK]

Thanks alot, man thats work absolutly perfect, just one more thing, how can I have disticnt count of order wher result is 1, in this case answer is 2 because only B and C have 1 in result column. how can I get this 2?

[Anonymous]

@TSK 

Once you have your expected [Result] column, you can distinctcount [Order], and set a filter to [Result] column: 

Measure = CALCULATE(DISTINCTCOUNT(Sheet1[Order]),FILTER(Sheet1,Sheet1[Result] = 1))

Best,
Paul