Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Count distinct values based on another column meeting a specific condition

Hello experts,

 

I am new on the forum and a newbie at PowerBi. So far I have managed to find answers to many of my questions through Google leading me here but I have been stuck trying stuff for the past day or so and would like to ask for your help. 

 

I have a table with assessment test results, with columns with a unique identifier for the test, a column for the questions, a column for the pre-test score, and a column for the post-test score. 

 

I want to count the number of assessments that have improved their overall score from the pre-test to the post-test but I can't figure out how to proceed.  Can anyone help figure out what DAX formula to use, or 

 

A simplified version of the table is below. In this example, the outcome would be 2 (assessments that improved).

 

I hope I am making my question clear. 

 

Assessment IDQuestionPre-test scorePost-test score

assessmentA1

Question 121
assessmentA1Question 211
assessmentB2Question 123
assessmentC3Question 234
assessmentC3Question 313
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    You are absolutely correct Anonymous I missed that bit sorry.

    Here you go:

    Improved unique assid only =
    COUNTX(
        Distinct(SELECTCOLUMNs( Filter('Ass Table',[Post-test score] > [Pre-test score]),"AssId",[Assessment ID]))
        ,[AssId]
    )

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks so much for your quick response. 

     

    However, I am unsure if the formula counts the unique assessment ID. Is it not counting the questions that have improved?  In your screenshot I see the same assessment ID twice. 

     

    Thanks,

    Susan

    • SamWiseOwl's avatar
      SamWiseOwl
      Icon for Super User rankSuper User
      2 years ago

      You are absolutely correct Anonymous I missed that bit sorry.

      Here you go:

      Improved unique assid only =
      COUNTX(
          Distinct(SELECTCOLUMNs( Filter('Ass Table',[Post-test score] > [Pre-test score]),"AssId",[Assessment ID]))
          ,[AssId]
      )

       

  • Sourav_M's avatar
    Sourav_M
    Frequent Visitor
    2 years ago

    Hi Anonymous ,

    Please use this DAX query:

    Improved_Count =
    VAR _temp =  SUMMARIZE(Sheet1,Sheet1[Assesment ID],"TotalPreScore",SUM(Sheet1[Pre-Test Score]),"TotalPostScore",SUM(Sheet1[Post-Test Score]))
    RETURN
        COUNTX(
        Filter(_temp, [TotalPostScore] > [TotalPreScore])
        ,[Assesment ID]
    )
     

     

  • SamWiseOwl's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous 
    Filter lets you test something for each row,

    CountX takes a table first argument then counts the field.

    Improved only =
    COUNTX(
        Filter('Ass Table',[Post-test score] > [Pre-test score])
        ,[Assessment ID]
    )