Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Count rows after filter with conditions

Hi, I hope I can explain myself to see if someone can help me with this.

 

I have a record of activities (a to-do list) exported from MS Planner, I need to check who has the worst performance out. This will be done with a label call "Reasignados" (reschedule tasks), I need to obtain the percentages of how many activities are people not completing therefore how many "Reasignados" task each of the people have and compare it to the amount of task they have assigned. Now I have both values working fine, because no task is assigned to the same person who originally had it. Taking in consideration tha this case can happen, I would like to fix this issue before I run into the problem. this is some example of the data I´m working with.

Here the column of the left (that does not show on the picture) is the Task name (Column 1), then o the right I have the person to who the task is assigned currently(Column 2), then the labels (where I have the lable "Reasignado")(Column 3), and last the person to whom the task was assigned originally(Column 4). Right now I count how many times the name X appears in Column 2 with a measurement, and I do the same for column 4, and then sum to ge the total of task that have been assigned to X currently and originally, but if I run into the case that Column 2 and column 4 have the same name X, I should not consider that row for the count in one of both columns (I rather ignored the row in count of column 4 as for having column 2 as a "full picture" of the tasks).

 

With all explained, my question is how to I ignore rows where column 2 and column 4 have the same value when im making the measurement in column 4? (In the picture the values aren´t the same but is just a matter of replacing values on the query, I´m still in an early development of this report).

 

This is the measurement I´m running on column 2 and 4 (just changed the column it is reffering to)

.

 

I would really appreciate if someone could help, as I do not work with BI that often.

 

Thanks

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Very sorry because I was a little stupid . I cannot understand your meaning correctly. Could you please provide more details with your desired output? 

     

    How to Get Your Question Answered Quickly 

     

    Best Regards

    Community Support Team _ Polly

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I´m sorry I didn´t explain my problem clearly, in a summarized explanation, I have two colums where I am looking for a name in each one, then I want to sum the total of times each name appears in both columns. The problem I have is I need to avoid the second column to count the rows where the contect is equal to column one.

       

      eg.

       

      Column 1      Column 2

      Monday        Monday

      Monday        Tuesday

      Monday        Wednesday

      Thursday       Monday

       

      I want to count the amount of times "Monday" appears in each column to use it in a pie chart, where I have count of Monday in column one and count of Monday in column two.

       

      In the example, I have Monday 5 times, but in row two, column 1 and 2 have the same value, I want the count in column two to avoid counting row 2 as the value is repeated.

       

      I hope my explanations is a little bit clearer now, feel free to ask any futher details, and thank you for the response!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

        I have created a simple sample, please refer to it to see if it helps you.

        Create three measures.

        Count column1 =Monday.

        count_1 =
VAR _1 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[column 1] = "Monday" )
    )
RETURN
    _1

        Then count column 2= Monday.

        measure =
VAR _1 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[column 1] = "Monday" )
    )
VAR _2 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[column 2] = "Monday" )
    )
VAR _3 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[column 1] = SELECTEDVALUE ( 'Table'[column 2] )
            && 'Table'[column 2] = "Monday"
    )
VAR _4 =
    IF (
        MAX ( 'Table'[column 1] ) = MAX ( 'Table'[column 2] )
            && MAX ( 'Table'[column 2] ) = "Monday",
        _2 - _3,
        _2
    )
RETURN
    _4

        count_2 =
MINX ( ALL ( 'Table' ), [measure] )

         

        Best Regards

        Community Support Team _ Polly

         

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