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

     

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

 

 

 



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

    This works great thank you. 

     

    How would I add more than one status types to count - I forgot we also have a Pending which needs to be included. I tried "Incomplete", "Pending" but it says - Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2.

     

    Would I just add a new column and mark Pending as Incomplete?

    • Anonymous's avatar
      Anonymous
      Not applicable
      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

       

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

        hi Anonymous 

         

        possibly easy enough but I tried amending your formula to show only those who have completed both courses, but its bringing up blank:

         

        Complete Both Courses =
            COUNTROWS(
                FILTER(
                    SUMMARIZE(
                        FILTER(
                            'Learning Item Status report',
                            'Learning Item Status report'[Status Overall] = "Complete"
                        ),
                        'Learning Item Status report'[Person Number],
                        "Complete Count",
                        COUNT('Learning Item Status report'[Status Overall])
                    ),
                    [Complete Count] = 2
                )
            )