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

projectFindings
x1
x1
x1
x1
y1
y2
y1
y1
z1
z2
z

1

 

I want the result to be the project ID that has only value =1. In this case, this should be project X. I will use this in count card and bar graph. Thanks!

Need Help

5 Replies

  • 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.

  • mussaenda's avatar
    mussaenda
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Fair-UL,

     

    This may not be the best solution but it achieves what you want:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUTJUitUhxKpEYRlhEYOwqlBYRshisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column1", Order.Ascending}, {"Column2", Order.Descending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Column2] = 2 then "Exclude"
else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
    #"Filled Down"
  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity 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.