cancel
Showing results for
Did you mean:

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

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

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",""))

``````

Thanks a lot for the help.

1 ACCEPTED SOLUTION
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",

"")))``````
5 REPLIES 5
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",

"")))``````
Community Support

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.
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],",")``````
Community Champion

Hi @tarocha

you can do it like this:

``````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)

Helper II

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",""))``````

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors