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 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.