Forum Discussion

Fair-UL's avatar
Fair-UL
Icon for Helper II rankHelper II
6 years ago

Filter a column by a specific value in another column

Hi. I have this mock data table project Findings x 1 x 1 x 1 x 1 y 1 y 2 y 1 y 1 z 1 z 2 z 1   I want the result to be the project ID that has ...
Need Help
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
6 years ago

You can count the number of such projects like this:

 

CountProjectsWithOnly1Findings =
COUNTROWS (
    FILTER (
        VALUES ( MockData[project] ),
        CALCULATE ( SELECTEDVALUE ( MockData[Findings] ) ) = 1
    )
)

 

Note that SELECTEDVALUE throws a blank if there are multiple values. 

  • Fair-UL's avatar
    Fair-UL
    Icon for Helper II rankHelper II
    6 years ago

    I tried this solution but it gives me all projects with value 1. I need projects with value=1 ONLY (meaning that if one project has both 1 and 2, I do not want it to be counted. 

    It is like I donot want anything in the intersection area to be counted. I hope this is clear now.

     

    Thanks for the quick response and the DAX

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      6 years ago

      If a project has both 1 and 2, then SELECTEDVALUE throws a blank and the condition should fail.

       

      If the formula isn't working, it might be that you are trying to use it in a context that doesn't include all of the rows associated with a project. For example, it probably won't work like you expect if you try to use it in a calculated column.