CODEWITHSUNDEEP
powerbidaxpowerquery
AyronH
AyronH

Reputation: 47

Count the number of occurrences after associated date of certain value in Power BI

I'm conducting an exercise around examining test results after tutoring has occurred. Essentially looking at the rates of "pass" post tutoring within the context of a given student. Where the ultimate outcome would be:

pass rate after tutoring = [count passes]/[count test date] WHERE test date > tutoring date.

For example:

enter image description here

Ideally, the final output of the measure would be = 1 (1/1)

Would anyone be able to point me in the direction of achieving this through a Power BI measure?

I've attempted the following to get the single oc with no luck:

Measure 3 = CALCULATE(COUNT(Table[Test Pass?]),FILTER(Table,Table[Test Date]>CALCULATE(Min(Table[Tutoring Date]),FILTER(Table,Table[Tutor (?)] <> BLANK ))))

Where I would then use the student column in a matrix with the measure to group pass rates post tutoring by student

Upvotes: 0

Views: 381

Answers (1)

Marcus
Marcus

Reputation: 4015

I have used this simple flat table data model:

enter image description here

You can calculate this with a measure that needs to be evaluated with your Student column:

Pass Rate After Tutoring =
VAR _tutor_date =
    CALCULATE (
        MAX ( 'Table'[Tutoring Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Student] )
    )
VAR _tests_post_tutor =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Student] ),
        'Table'[Test Date] > _tutor_date
    )
VAR _successes =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Student] ),
        'Table'[Test Date] > _tutor_date,
        'Table'[Test Pass]
    )
RETURN
    DIVIDE ( _successes, _tests_post_tutor )

But this assumes that students are only tutored for one specific test, and are tutored once.

Upvotes: 2

Related Questions