Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table that looks like below:
StudentID | Test1 | Test2 | Test3 | Test4 | Test5 |
1 | 93 | 71 | 56 | 66 | 60 |
2 | 59 | 54 | 58 | 78 | 51 |
3 | 95 | 59 | 81 | 74 | 85 |
4 | 82 | 58 | 91 | 86 | 59 |
5 | 63 | 100 | 88 | 92 | 50 |
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.
StudentID | Test1 | Test2 | Test3 | Test4 | Test5 |
1 | 93 | 71 | 56 | 66 | 60 |
3 | 95 | 59 | 81 | 74 | 85 |
4 | 82 | 58 | 91 | 86 | 59 |
5 | 63 | 100 | 88 | 92 | 50 |
Here's my failed attempt:
CALCULATETABLE(Score, Test1>=80 || Test2>=80 || Test3>=80 || Test4>=80 || Test5>=80)
Thank you,
Solved! Go to Solution.
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |