Forum Discussion

TSK's avatar
TSK
Frequent Visitor
6 years ago
Solved

Result based on rows against 1 unique value

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
TSK
Frequent Visitor
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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