Forum Discussion
Calculate() filtered by another measure
Hello,
I am trying to filter a calculate() statement with another calculated measure. I have looked at serveral previous questions, but those solutions did not seem to work. Any assistance with my question below or pointing me toward the correct solution in the forum is most appreciated.
My dataset has the following structure:
| Year | Class | ClassMovement | Name |
| 2015 | 4 | 0 | John |
| 2016 | 4 | 0 | John |
| 2017 | 4 | 0 | John |
| 2015 | 1 | 0 | Sarah |
| 2016 | 2 | 1 | Sarah |
| 2017 | 3 | 1 | Sarah |
| 2016 | 5 | 0 | Amit |
| 2017 | 5 | 0 | Amit |
| 2015 | 1 | 0 | Nicole |
| 2016 | 1 | 0 | Nicole |
| 2017 | 2 | 1 | Nicole |
First I created a calculated measure to identify years with no movement (see below).
MoveCalcFilter = CALCULATE(DISTINCTCOUNT(table[Year]), FILTER(table, table[ClassMovement] = 0))
Adding the measure "MoveCalcFilter" to the 'Name' colum results in the following:
| Name | MoveCalcFilter |
| John | 3 |
| Sarah | 1 |
| Amit | 2 |
| Nicole | 2 |
Next, I want to used create another calculated measure to count the number of distinct names for which there is a MoveCalcFilter value of three. I tried following calculation, but it resulted in only blank values.
NoMoveCount = CALCULATE(DISTINCTCOUNT(table[Name], FILTER(table,table[MoveCalcFilter] = 3))
The ideal output would yield:
| Class | NoMoveCount |
| 4 | 1 |
Any assistance would be most appreciated. I thank you for your time. Please feel free to reach out if additional information would be helpful.
Cheers!
