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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
tarocha
Helper II
Helper II

Filter with three conditions

Hello can you help me?


I have a column calculated to filter students who have not taken either class or another.

I would like to include one more condition to search for those who did not take 3 classes could you help me? I'm not able to add another condition.


I would like to filter, for example, students who did neither class 1 nor class 2 nor class 3.

This is the pbix I am using as an example
https://drive.google.com/file/d/16PN12shTogdJJ9b1w7v75E6cZOn4RNBV/view?usp=sharing

I'm using this formula.

 

Column = 



if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 1" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&'Table'[CLASS]="CLASS 2"),'Table'[DONE])="NOT","Not for class1&2",if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 2" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&'Table'[CLASS]="CLASS 3"),'Table'[DONE])="NOT","Not for class2&3",""))


 

 

 

apagar.JPG

 

Thanks a lot for the help.


1 ACCEPTED SOLUTION
tarocha
Helper II
Helper II


I was using the wrong filter for the third condition, instead of using || was using &&.
Now working.

This is the code

Column = 

if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 1" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&('Table'[CLASS]="CLASS 2"||'Table'[CLASS]="CLASS 3")),'Table'[DONE])="NOT","Not for class1&2&3",

if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 1" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&'Table'[CLASS]="CLASS 2"),'Table'[DONE])="NOT","Not for class1&2",


if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 2" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&'Table'[CLASS]="CLASS 3"),'Table'[DONE])="NOT","Not for class2&3",


"")))

View solution in original post

5 REPLIES 5
tarocha
Helper II
Helper II


I was using the wrong filter for the third condition, instead of using || was using &&.
Now working.

This is the code

Column = 

if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 1" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&('Table'[CLASS]="CLASS 2"||'Table'[CLASS]="CLASS 3")),'Table'[DONE])="NOT","Not for class1&2&3",

if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 1" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&'Table'[CLASS]="CLASS 2"),'Table'[DONE])="NOT","Not for class1&2",


if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 2" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&'Table'[CLASS]="CLASS 3"),'Table'[DONE])="NOT","Not for class2&3",


"")))

Hi @tarocha ,

Thanks for sharing the codes. If the problem has been resolved, could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User
Super User

This can be simplified a bit like this:

 

Column = 
var n = 'Table'[NAME]
var v = CALCULATETABLE(filter(all('Table'),'Table'[NAME]=n && 'Table'[DONE]="NOT"))
return CONCATENATEX(v,[CLASS],",")
FrankAT
Community Champion
Community Champion

Hi @tarocha 

you can do it like this:

03-10-_2020_23-39-40.png

 

 

 

Measure = 
VAR _Table =
    FILTER(
        'Table',
        'Table'[DONE] = "NOT" && 
        'Table'[CLASS] = "CLASS 1" || 
        'Table'[CLASS] = "CLASS 2" || 
        'Table'[CLASS] = "CLASS 3" 
    )
VAR _Count = COUNTX(_Table,'Table'[NAME])    
RETURN
    IF(_Count = 3 , "Not in Class 1, 2 , 3", BLANK())

 

 

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Hi, @FrankAT 

 

I need it to be a calculated column for you to use as a slicer.
I was able to make two conditions with the dax code below, but I'm having trouble including one more condition for another class.

 

 

 

Column = 
if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 1" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&'Table'[CLASS]="CLASS 2"),'Table'[DONE])="NOT","Not for class1&2",if('Table'[DONE]="NOT"&&'Table'[CLASS]="CLASS 2" && MAXX(FILTER('Table','Table'[NAME]=EARLIER('Table'[NAME])&&'Table'[CLASS]="CLASS 3"),'Table'[DONE])="NOT","Not for class2&3",""))

 


apagar.JPG

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.