Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX function to calculate Coverage

I have table with multiple columns, the ones of interest are course_id, session_id and total_score. There are multiple entries for each combination of course_id and session_id. What I need is a DAX function to calculate the coverage defined by count of unique combinations of course_id and session where total_score is not null divided by count of unique combinations of course_id and session. This is what I have but it is not returning right results

```

Evaluated Sessions Coverage =
DIVIDE(
countrows(SUMMARIZE(FILTER('evaluacion_endetus_merged', not(isblank(evaluacion_endetus_merged[total_score]))), evaluacion_endetus_merged[course_id],evaluacion_endetus_merged[session])),
countrows(SUMMARIZE('evaluacion_endetus_merged', evaluacion_endetus_merged[course_id],evaluacion_endetus_merged[session]))
)

```

 

  • Anonymous , Do you need all combination, cross Join

     

    Evaluated Sessions Coverage =
    DIVIDE(
    countrows(SUMMARIZE(FILTER('evaluacion_endetus_merged', not(isblank(evaluacion_endetus_merged[total_score]))), evaluacion_endetus_merged[course_id],evaluacion_endetus_merged[session])),
    countrows(Crossjoin(Distinct( evaluacion_endetus_merged[course_id]),Distinct( evaluacion_endetus_merged[session]))
    ))

2 Replies

  • Anonymous , Do you need all combination, cross Join

     

    Evaluated Sessions Coverage =
    DIVIDE(
    countrows(SUMMARIZE(FILTER('evaluacion_endetus_merged', not(isblank(evaluacion_endetus_merged[total_score]))), evaluacion_endetus_merged[course_id],evaluacion_endetus_merged[session])),
    countrows(Crossjoin(Distinct( evaluacion_endetus_merged[course_id]),Distinct( evaluacion_endetus_merged[session]))
    ))

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks, I tried this one but same issue as before, it does not show course_ids which have 0  coverage