Forum Discussion

Anonymous9999's avatar
Anonymous9999
New Member
3 years ago
Solved

Taking Distinct count after calculating a measure

I have a database for students. Student name, school joining date, teacher assigned,
Date of termination, expected graduation date.
Here since I have to take count for every date column by month, I have created a calendar table and gave direct relationship with joining date of student
As we know same teacher teaches many students, we need cumulative distinct count of the teacher after deducting
the count of students terminated and graduated during that month.Below is my dataset

Joining DateStudent nameTeacherTerminated dateGraduated date
1/1/2022AI 3/1/2022
1/2/2022BJ  
2/3/2022DI3/25/2022 
2/4/2022EK  
3/5/2022FK4/2/2022 
4/5/2022GL  
4/5/2022HM  

 

Expected result

Jan2  
Feb3  
March2 we 1st consider all students joined until march.then we have to ignore the row which was terminated and graduated in march. Then consider all the teachers from Jan to march and take their distinct count 
April4 Same as march calculation, we ignore any termination and graduations happned in april and then take disticnt count
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous9999 

     

    try to create plot a table visual with the measure like this:

    Result = 
VAR CurrentMonth = MONTH(MAX(TableName[Joining Date]))
RETURN
CALCULATE(
    DISTINCTCOUNT(TableName[Teacher]),
    FILTER(
        ALL(TableName),
        MONTH(TableName[Joining Date])<=CurrentMonth            
    )
) -
CALCULATE(
    DISTINCTCOUNT(TableName[Teacher]),
    FILTER(
        ALL(TableName),
        OR (
            AND ( 
                MONTH(TableName[Terminated Date])<>BLANK(), 
                MONTH(TableName[Terminated Date])=CurrentMonth
            ),
            AND( 
                MONTH(TableName[Graduated Date])<>BLANK(), 
                MONTH(TableName[Graduated Date])=CurrentMonth
            )
        )
    )
)

     

    i tried and it worked like this:

     

    the dataset:

     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous9999 

     

    try to create plot a table visual with the measure like this:

    Result = 
VAR CurrentMonth = MONTH(MAX(TableName[Joining Date]))
RETURN
CALCULATE(
    DISTINCTCOUNT(TableName[Teacher]),
    FILTER(
        ALL(TableName),
        MONTH(TableName[Joining Date])<=CurrentMonth            
    )
) -
CALCULATE(
    DISTINCTCOUNT(TableName[Teacher]),
    FILTER(
        ALL(TableName),
        OR (
            AND ( 
                MONTH(TableName[Terminated Date])<>BLANK(), 
                MONTH(TableName[Terminated Date])=CurrentMonth
            ),
            AND( 
                MONTH(TableName[Graduated Date])<>BLANK(), 
                MONTH(TableName[Graduated Date])=CurrentMonth
            )
        )
    )
)

     

    i tried and it worked like this:

     

    the dataset:

     

  • Anonymous9999's avatar
    Anonymous9999
    New Member
    3 years ago

    This did not work for me. It is giving very different number which I am unable to identify how it is coming