Filter words from two columns and display the result in one table

[Mesku01]

Hello Dears,

I am using powerapps to collect user information and sharepoint online as datasource.I would like to filter two columns and display the result as one as listed below using power bi, Could you please advice 🙂.

The column Interested in only the following , is a multichoice column (The user can select morethan one value) and the column Interested in all course is a dropdown column (YES,NO)

Name	Student ID	Interested in all course	Interested in only the following
ABC	1234	NO	Accounting,IT
DEF	5678	NO	Law,Marketing
GHI	9810	NO	IT,Engineering,Law
JKL	112313	NO	Marketing,Accounting
MNO	141516	YES
PQX	171819	YES
YZA	202122	YES

course Types

Accounting

Law

IT

Engineering

Law

Marketing

In power bi slicer (Course selected ) , Example : Accounting ,should show all student that prefer to take accounting course including those student that 

select Interested in all course as YES.

Thanks in advance for your help.

[Mesku01]

Hi @v-jingzhang ,

Could you please check my questions? Thanks in advance!

[v-jingzhang]

Hi @Mesku01 

Firstly, please check if the attached file meets your first need.

Secondly, do you want to show all matching results in a single report page? Since some students have multiple interested courses, they will need to appear in multiple course groups.

And do you have a student table in your model which records those students info to be displayed?

[Mesku01]

Hi @v-jingzhang 

Thank you so much for your time, the attached file works for my current requirement.

Secondly, do you want to show all matching results in a single report page?

Yes,

And do you have a student table in your model which records those student’s info to be displayed?

No, I only have one table that records student details (Name, age, current location etc.) Captured using PowerApps form. The form contains multichoice column i.e., interested in the following course and single text field interested in all courses.

Thanks!

[Mesku01]

Is it possible to have an opption to view From Course Type to Course type , Ex. Two Slicer that help to See possible course switch from Accounting to IT and from IT to Accounting, instead of the Name slicer?

[v-jingzhang]

Hi @Mesku01 

You can add the following measure as a filter on the table visual and set it to show items when value is 1. I have attached the sample pbix at bottom. You can download it to see details. 

Measure = 
VAR _selectedCourse = SELECTEDVALUE(Courses[course Types])
RETURN
SWITCH(TRUE(),
SELECTEDVALUE('Table'[Interested in all course ])="YES",1,
SELECTEDVALUE('Table'[Interested in all course ])="NO",IF(CONTAINSSTRING(SELECTEDVALUE('Table'[Interested in only the following]),_selectedCourse),1,0),
0)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Mesku01]

Hi v-jingzhang,

Thanks a lot, it answers my question. Is it possible to get the total count of the selection? Ex. Total course selected by course category?

And Second question : View the matching result as popup screen/table like the below sample :

Name	Student ID	Interested in all university	Interested in only the following	Current course
ABC	1234	NO	Accounting,IT	Marketing
DEF	5678	NO	Law,Marketing	Law
GHI	9810	NO	IT,Engineering,Law	Marketing
JKL	112313	NO	Marketing,Accounting	IT
MNO	141516	YES		Marketing
PQX	171819	YES		Law
YZA	202122	YES		IT
Slicer Selection(Course Type)
Accounting
First Filter Result
Name	Student ID	Interested in all university	Interested in only the following	Current course
ABC	1234	NO	Accounting,IT	Marketing
JKL	112313	NO	Marketing,Accounting	IT
MNO	141516	YES		Marketing
PQX	171819	YES		Law
YZA	202122	YES		IT
Final Result : Switch student based on course interest
Name	Student ID	Interested in all university	Interested in only the following	Current course
ABC	1234	NO	Accounting,IT	Marketing
JKL	112313	NO	Marketing,Accounting	IT

Thanks

[v-jingzhang]

Hi @Mesku01 

Sorry I don't 100% understand. What is the "Total course selected by course category"? Do you mean that you might select multiple courses in the course slicer at one time? If so, you can use below measure to get the number of selected ones:

Measure 2 = COUNTROWS(Courses)

In this scenario, my original measure needs to be modified. 

For the second question, do you want something like Power BI custom tooltip page on a visual?

Create report tooltip pages in Power BI - Power BI | Microsoft Learn

Best Regards,

Jing

[Mesku01]

@v-jingzhang Thank you very much for your reply.

1. For the second question, do you want something like Power BI custom tooltip page on a visual?

I want to see students who wants to switch for example from accounting to IT and students who wants to change their course from IT to accounting. the administrator will use different filtering criteria like age, course grade ,course type (slicers ) once done he administrator will view matching result , The screen will show the  matching rows that match the filter criterieas of the student and the administrator will get information about Which student wants to exchange course with another student . Since there might be many students matching the selection criteria ,The administrator will do a selections of the students. 

After selecting the students , a report will pop up to show all possible candidates. 

Please let me know if you still need additional information.

Thanks.

[v-jingzhang]

Hi @Mesku01 

You can add a Name slicer to the report and put the same filter measure on it to filter items when value is 1. The names displayed in the name slicer will be consistent with those in the table visual. Then use the name slicer to select the students you want to view. 

Best Regards,

Jing

[Mesku01]

Thanks , I will try it now and will let you know.

[Mesku01]

Is it possible to add a slicer that show course change between students i.e Who wants to change from Accounting to IT and from IT to Accounting , Because if the selection is made by student name , it will be long list (3000 students).  Thanks.

[v-jingzhang]

Hi @Mesku01 

Do you mean that we need to see the current course type slicer as a "To Course" slicer, and add another "From Course" slicer which is based on "Current Cource" column?

[Mesku01]

Yes, To View as from Course to Course slicer . Thanks. 

[v-jingzhang]

Hi @Mesku01 Please check the new file. 

[Mesku01]

Thanks , I am checking. How did you create course 2? 

[v-jingzhang]

It's a calculated table. You can see its formula in the formula bar. Or you can also use Enter data to create it or duplicate Courses table in PQ. 

[Mesku01]

Thank you very much! Once we got the filter value (Matching results of 1000 ) , Is there a way to 1. Select for example 10 students (Using Checkbox or other option)

2. Generate a report that shows group by Course Type, Student name, Index No ?
Ex ,
Matching Results
Form Accounting to IT
Student Name 1 ,Index,Age
Student Name 2 ,Index,Age
From Marketing to IT 
Student Name ,Index,Age
Student Name ,Index,Age

Thank you so much for your unreserved support!