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 Column only B has "Ali" and  "Saqib" so result should be 1 only for B. How to achieve this?

 

OrderNameResult
AAli0
AMuhammad0
AAshraf0
BAli1
BSaqib1
CAli0
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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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