cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pucities
Frequent Visitor

CALCULATETABLE and OR (double pipes)

 

I have a table that looks like below: 

StudentIDTest1Test2Test3Test4Test5
19371566660
25954587851
39559817485
48258918659
563100889250

 

I want to create a filtered table that has the same columns except it only has students who have 80 or above on at least one test. 

 

In my example, since StudentID =2  has never had a test score higher than 80, it should be excluded in the new table.

 

StudentIDTest1Test2Test3Test4Test5
19371566660
39559817485
48258918659
563100889250

 

 

Here's my failed attempt: 

 

 

CALCULATETABLE(Score, Test1>=80 || Test2>=80 || Test3>=80 || Test4>=80 || Test5>=80)

 

 

 

 

 

Thank you,

 

1 ACCEPTED SOLUTION

Hi @pucities ,

 

Check the file again, I've changed it.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

5 REPLIES 5
camargos88
Super User III
Super User III

Hi @pucities ,

 

Check this file as an example: Download PBIX 

 

I've unpivoted the value columns, so you can filter it. Also I've created another table as example, if necessary.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Thank you for your reply. 

 

Sorry I haven't been clear. 

 

But I want the new table to have the same columns. Also, I just want to include all 5 scores for students who had >=80 on at least one test.

 

 

Hi @pucities ,

 

Check the file again, I've changed it.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Thank you!!

 

Do you know if there's any way to do it without pivot/unpivot the table just using DAX? 

Hi @pucities ,

 

Yes, it's possible to do it using only DAX. It's the same code, you need to change the filter for your columns with or condition.

However you are gonna have to type all columns always for any calculation.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors