Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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.
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
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
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
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.
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
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:
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
Proud to be a 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
Proud to be a Super User! |
|
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
76 | |
49 |
User | Count |
---|---|
143 | |
140 | |
109 | |
69 | |
55 |