Forum Discussion

sedric1's avatar
sedric1
Icon for Helper III rankHelper III
5 years ago
Solved

Need help creating counting measures per criteria

I have a table imported from Excel that is structured with the following structure below (important fields listed below).   The background: We have a student ID who is tied to 1 or more programs. E...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi sedric1 ,

     

    I suggest you create a summarize table and :

    Table2 = SUMMARIZE('Table','Table'[Program],'Table'[User ID],"Pass",IF(CALCULATE(COUNT('Table'[Exam]),FILTER('Table','Table'[Score]<80&&'Table'[Item Type] = "Proficiency"))>0,"NO","YES"))

PASSALL = IF("NO" in CALCULATETABLE(VALUES('Table2'[Pass]), ALLEXCEPT(Table2,Table2[User ID])),0,1)

     

     

    Then you can use the following two measure :

     

    Measure1 = COUNTX(FILTER(Table2,Table2[Pass] = "YES"),'Table2'[User ID])+0
Measure2 = COUNTX(FILTER(Table2,Table2[PASSALL] = 1),Table2[User ID])

     

     

    For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWES67XnUUBLmqExJgIGR1YBs1ltHBgBSRHdWjZq6odbHQ?e=K0JtXw

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi sedric1 ,

 

I suggest you create a summarize table and :

Table2 = SUMMARIZE('Table','Table'[Program],'Table'[User ID],"Pass",IF(CALCULATE(COUNT('Table'[Exam]),FILTER('Table','Table'[Score]<80&&'Table'[Item Type] = "Proficiency"))>0,"NO","YES"))

PASSALL = IF("NO" in CALCULATETABLE(VALUES('Table2'[Pass]), ALLEXCEPT(Table2,Table2[User ID])),0,1)

 

 

Then you can use the following two measure :

 

Measure1 = COUNTX(FILTER(Table2,Table2[Pass] = "YES"),'Table2'[User ID])+0
Measure2 = COUNTX(FILTER(Table2,Table2[PASSALL] = 1),Table2[User ID])

 

 

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWES67XnUUBLmqExJgIGR1YBs1ltHBgBSRHdWjZq6odbHQ?e=K0JtXw

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

  • sedric1's avatar
    sedric1
    Icon for Helper III rankHelper III
    5 years ago

    v-deddai1-msft  Thanks so much for the reply!

    It did not work for me once I applied the summarized table, column and measures to my entire file. I do think I am closer with the summarized table but it is counting too many user IDs (measure 2) so I am trying to see if maybe its not pulling in distinct values or something else now.

  • sedric1's avatar
    sedric1
    Icon for Helper III rankHelper III
    5 years ago

    v-deddai1-msft To followup on this, I did some tweaking to the logic per my full data file and got it to work using your method. Thanks again for the help as I was totally stuck without you.