Forum Discussion

hulk_deka's avatar
hulk_deka
Frequent Visitor
4 years ago
Solved

CHOOSING ROWS WITH PARTICULAR VALUE.

i have two columns with the following values

col A  col B

a         1

b         2

c         2

d         1

e         2

 

I would like to have a count from column B with rows that have only '2' as an entry.

I.E in this case 3 is the answer i am looking for

i have been using this formula : 

M = COUNTROWS(Query1,Query1[col B]="2")
 
but it doesnt seem to work. 
Looking forward to some insights.
Rishav 

 

Need Help
Show and Tell
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi hulk_deka ,

    According to your description, here's my solution. 

    Create a measure.

    M =
COUNTROWS ( FILTER ( 'Query1', 'Query1'[col B] = 2 ) )

    Get the result. 

    As you say "column B with rows that have only '2' as an entry", I'm not sure if you mean for example the below sample, the 2 of e should not be included, as it still as a value 1. 

    If this is the case, modify the formula to:

    Measure =
CALCULATE (
    DISTINCTCOUNT ( Query1[col A] ),
    FILTER (
        'Query1',
        COUNTROWS (
            FILTER (
                'Query1',
                'Query1'[col A] = EARLIER ( 'Query1'[col A] )
                    && 'Query1'[col B] <> 2
            )
        ) = 0
    )
)

    Get the correct result according to the modified sample.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Use this one 

    = List.Count(List.Select(Query1[col B], each _=2 or _="2"))

     

    • hulk_deka's avatar
      hulk_deka
      Frequent Visitor
      4 years ago

      is there any way we can use it as a measure? the above formula doesnt seem to be accepted as a measure and gives out an error.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi hulk_deka ,

    According to your description, here's my solution. 

    Create a measure.

    M =
COUNTROWS ( FILTER ( 'Query1', 'Query1'[col B] = 2 ) )

    Get the result. 

    As you say "column B with rows that have only '2' as an entry", I'm not sure if you mean for example the below sample, the 2 of e should not be included, as it still as a value 1. 

    If this is the case, modify the formula to:

    Measure =
CALCULATE (
    DISTINCTCOUNT ( Query1[col A] ),
    FILTER (
        'Query1',
        COUNTROWS (
            FILTER (
                'Query1',
                'Query1'[col A] = EARLIER ( 'Query1'[col A] )
                    && 'Query1'[col B] <> 2
            )
        ) = 0
    )
)

    Get the correct result according to the modified sample.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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