Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Whibley92
Helper II
Helper II

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. 

1 ACCEPTED SOLUTION

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.

vheqmsft_0-1709601739516.png

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

vheqmsft_1-1709601804163.png

 

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

 

View solution in original post

8 REPLIES 8
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1709532369627.png
Course 2 unfinished

vheqmsft_1-1709532389629.png
1.Ensure that the names of the trainees are related between the two tables

vheqmsft_2-1709532467487.png

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

vheqmsft_3-1709532644341.png
4.Final output

vheqmsft_4-1709532690432.png

 

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

 

 

 

 

 



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

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:

vheqmsft_0-1709546121999.png

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

vheqmsft_1-1709546200195.png

Final output

vheqmsft_2-1709546215931.png



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

 

 

 



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?

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.

vheqmsft_0-1709601739516.png

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

vheqmsft_1-1709601804163.png

 

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

 

hi @v-heq-msft 

 

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
        )
    )
PijushRoy
Super User
Super User

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




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





bhanu_gautam
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.