Forum Discussion
How to filter ID's 'sometimes'
Hi all,
I can't get through something I need. The issue is that I have an amount of people doing a subcourse of a Master. But sometimes, they also appear in the row with 'blanks'. For instance my summarized values are:
| 2020 | |
| Master Business Administration | 4 |
| <Blank> | 3 |
| Course A | 1 |
| Course B | 2 |
When you look at the individuals, the systems spits out some irregularities:
| Student ID | Course | Influx |
| 101 | <Blank> | 1 |
| 101 | Course A | 1 |
| 102 | <Blank> | 1 |
| 102 | Course B | 1 |
| 103 | <Blank> | 1 |
| 104 | Course B | 1 |
So 101 is known as a <Blank> and as Course A. On the total level of the Master I made a Distinctcount. This works fine. But: I want the students that have a subcourse to be subtracted from the Total in <Blank>. So in this case with the individuals the total of <Blank> should be 1 because only ID 103 should count. 101 and 102 are in Courses A and B and should not be counted as a <Blank>.
So what I would need is:
| 2020 | |
| Master Business Administration | 4 |
| <Blank> | 1 |
| Course A | 1 |
| Course B | 2 |
Please help...
Best, Rob
2 Replies
- amitchandak
Super User
Rob_Alexander , try a measure like
sumx(Table,if(isblank(max([Course])),1,distinctcount(Table[Student ID]))
Too bad, it does not work. I had to make it into a countx, but I get that the Blanks and the Courses are added up in the total. So in this example it gives me:
2020 Master Business Administration 6 <Blank> 3 Course A 1 Course B 2
