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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
confidentnwrong
Advocate I
Advocate I

Filter a table with two slicers on the same field

Hi all,

I am trying to have a table that displays students who got a minimal grade in 2 subjects.

 

I want to have 2 slicers to choose the subjects, then 2 other slicers to choose the minimal grade, and a table displaying all the students who these conditions apply to. I'm having trouble filtering through the 2 subjects, I'll look into the grades later (but it forces me to use a slicer per subject)

 

So for instance, if I have no filter on, the table looks like :

namesubject grade
amaths50
aphysics70
bmaths90
bphysics80
cmaths100
cchemistry40
cpoetry50
dmaths40
emaths30
ephysics80
echemistry70

 

Then if I put the first slicer on physics and the other one on maths, I'll have all the people who took maths AND physics and their grades in those subjects only, so :

namesubject grade
amaths50
aphysics70
bmaths90
bphysics80
emaths30
ephysics80

 

So far all I've managed to do was making 2 tables, and filtering each by a filter. If I filter by both slicers at the same time, then I get nothing displayed because maths =/= physics.

 

This is what my model basically looks like:

 

ModelModel

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@confidentnwrong , Refer if this can help

Compare Categorical Data Using Slicers, Compare Two Brands/Categories/Cities: https://youtu.be/exN4nTewgbc

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@confidentnwrong , Refer if this can help

Compare Categorical Data Using Slicers, Compare Two Brands/Categories/Cities: https://youtu.be/exN4nTewgbc

I miswrote my parenthesis in the last message ! Thank you for the help.

 

Afterwards I got an issue where the grades displayed where only the ones of the table dim_class, even though the names displayed came from both dim_class and Class_new.

In case this helps anyone, I'm posting how I was able to solve this :

I ended up creating 2 tables based on the dim_class table, Class_new and Class_2 using

Class_new = 'dim_class'

Then I created measures to select the classes and the grades based on the names, one per new table

Selection = 
Var comp_1 = SELECTEDVALUE('Class_new'[Name_class])
Return CALCULATE(SUM('fact_results'[grade]), 'dim_class'[Name_class] = comp_1 )

And thus when displaying in a table, I choose the fields : 

'dim_student'[Name_student]

'measures'[Selection]

'class_new'[Name_class]

'measures'[Selection2]

'class_2'[Name_class]

Finally, by using slicers on 'class_new'[Name_class] and 'class_2'[Name_class], I can get my results 🙂

 

Remark : This solution displays all the grades of all the students taking either classes !

Thank you for the help

 

However I can not seem to adapt the measures to my problem. 

I followed along the video and made a Class_new table with the same info as the existing Class table, but when it comes to creating the measure Class_Name 2, I get the error "A 'FILTER' function was used in a True/False expression as a table filtre expression. This is unauthorized."

Here is how I wrote the measure :

Class_name 2 = CALCULATE([Name_class]
FILTER(ALL('dim_class'), 'dim_class'[ID_class]) in ALLSELECTED(Class_new[ID_Class])
)

And here's how I created the Class_new table :

Class_new = 'dim_class'

Do you know where I went wrong ?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.