Forum Discussion

Whibley92's avatar
Whibley92
Icon for Helper II rankHelper II
2 years ago
Solved

Filtering/duplicates/count words - not sure where this would sit in Power BI topics

I have a spreadsheet with a list of 2 courses, learners names and completion status.  

I have made one table to show learners incomplete in one course.

I have made another table to show learners incomplete on the other course.

 

I want a table to show learners who are incomplete on both courses.

 

I really don't know how to do this. 

data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Whibley92 ,
    As you said, there can only be two arguments after the filter, so if you want to make a judgment based on two conditions, then you can use the logical operator || to splice the two arguments together.

    Measure

    Incomplete Both Courses = 
    COUNTROWS(
        FILTER(
            SUMMARIZE(
                FILTER(
                    'Table',
                    'Table'[Status] = "Incomplete" || 'Table'[Status] = "Pending"
                ),
                'Table'[Learner number],
                "Incomplete Count", 
                COUNT('Table'[Status])
            ),
            [Incomplete Count] = 2
        )
    )

    Final output

     

    Best regards,

    Albert He

     

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

     

8 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Whibley92 , Try using below method

     

    Result Measure = IF ( COUNTROWS ( FILTER ( YourTableName, [Group 2] = "X" ) ) > 0, "Red", "Green" )

     

    Please accept as solution and give kudos if it helps

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Whibley92 

    If you are using Power Query
    Please Append the two table as NEW table
    Then keep only Duplicate rows in Learner Names. This will be third table with Name both course incomplete.



    Let me know if that works for you


    If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

    Thanks
    Pijush
    Linkedin

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Whibley92 ,
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:

    Course 1 unfinished


    Course 2 unfinished


    1.Ensure that the names of the trainees are related between the two tables

    2.Create a measure

    Incomplete Both Courses = 
    CALCULATE(
        COUNTROWS('Course 1 unfinished'), 
        FILTER('Course 1 unfinished', 
        'Course 1 unfinished'[Status] = "Incomplete" && RELATED('Course 2 unfinished'[Status]) = "Incomplete"
        )
    )

    3.Apply measure as filter of the table


    4.Final output

     

    Best regards,

    Albert He

     

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

     

     

     

     

     



    • Whibley92's avatar
      Whibley92
      Icon for Helper II rankHelper II
      2 years ago
      Is there a way to do this if all the results were in one table? Learner number Course title Status #1 Course 1 Complete #2 Course 1 Incomplete #3 Course 1 Complete #4 Course 1 Complete #5 Course 1 Complete #6 Course 1 Complete #1 Course 2 Incomplete #2 Course 2 Incomplete #3 Course 2 Complete #4 Course 2 Incomplete #5 Course 2 Incomplete #6 Course 2 Complete
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Whibley92 ,
        Here some steps that I want to share, you can check them if they suitable for your requirement.
        Here is my test data:

        Create a measure

        Incomplete Both Courses = 
    COUNTROWS(
        FILTER(
            SUMMARIZE(
                FILTER(
                    'Table',
                    'Table'[Status] = "Incomplete"
                ),
                'Table'[Learner number],
                "Incomplete Count", 
                COUNT('Table'[Status])
            ),
            [Incomplete Count] = 2
        )
    )

        Apply to the filter

        Final output



        Best regards,

        Albert He

         

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