Forum Discussion

Fair-UL's avatar
Fair-UL
Helper 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
v-jingzhang's avatar
v-jingzhang
Community Support
6 years ago

Hi Fair-UL 

 

Try this measure to achieve the project ID with only value 1.

Project_ID =

CALCULATE (

    IF (

        MINX ( Project, IF ( SELECTEDVALUE ( Project[Findings] ) = 1, 1, 0 ) ) = 1,

        SELECTEDVALUE ( Project[Project] ),

        BLANK ()

    ),

    ALLEXCEPT ( Project, Project[Project] )

)

 

Or another way in case you need, create calculated columns to count the number of values for each project and mark whether a project has only one value 1. In this way, you can use filters/slicers or other methods to filter the projects with only value 1.

Count Of Values =

CALCULATE (

    DISTINCTCOUNT ( Project[Findings] ),

    FILTER (

        Project,

        Project[Project]

            = EARLIER ( Project[Project] )

    )

)



Has Only Value 1 =

IF (

    AND ( Project[Count Of Values] = 1, Project[Findings] = 1 ),

    "Yes",

    BLANK()

)

 

 

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.