Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sandy2405
Frequent Visitor

How to count the values of a Measure

Hi,

 

I have a dataset (smaple dataset is here) of participant's scorecard in each test, they have attempted. I want to categorized the participant, based on the no. of test, they have attempted, like below:

  • Less than 5 Test Attempted 
  • 5 to 8 Test Attempted
  • 9 to 12 Test Attempted
  • 13 to 17 Test Attempte

I have dataset with below columns:

 

Capture3.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I achieved the required output in SQL server, using below query:

Capture4.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In DAX, i have followed below steps:

 

1. Calculate (Measure) the count of the test attempted by each participant:

   AttemptedTestCounts = DISTINCTCOUNT('Test scores'[Test Name])

 

2. Create (Measure) a test category by using IF statements:

    Test Category =
    IF(AND([AttemptedTestCounts] >= 1, [AttemptedTestCounts] <=4), "Less than 5 Assessments Attempted",
    IF(AND([AttemptedTestCounts] >= 5, [AttemptedTestCounts] <=8), "5 to 8 Assessments Attempted",  
    IF(AND([AttemptedTestCounts] >= 9, [AttemptedTestCounts] <=12), "9 to 12 Assessments Attempted", 
    IF(AND([AttemptedTestCounts] >= 13, [AttemptedTestCounts] <=17), "13 to 17 Assessments Attempted",
    "No Assessment Attempted"
    ))))
 
3. By following these two steps, I achieved the data in below format:Capture5.JPG
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  • Now, I have to calculate the count of each category. But, I am unable to do that, beacuse category is a Measure and we can't use measure in COUNT function.
  • I have tried same operation using Computed Column, but when I used count column in IF statement, it always return 1 for each pariticipant. 
  • I also tried calculate (Computed Column) the count of test for each row using ALLSELECTED(), but when I used count column in IF statement, it always return 1 for each pariticipant.

 

Please help me here on below points:

  1. How can I get the count of the values in a Measure.
  2. Why computed count (Computed Column) is getting 1 for each participant, when it used in IF statement.
  3. How can I convert my SQL query into DAX.

 

Thanks in Advance,

Sandy

 
 
1 ACCEPTED SOLUTION

Hi @sandy2405

 

You may try below formula to get the column:

Test Category = (
                 var AttemptedTestCounts = CALCULATE(DISTINCTCOUNT('Test Score'[Test Name]),ALLEXCEPT('Test Score','Test Score'[Participant identifier]))
                 return (
                             IF(AND(AttemptedTestCounts >= 1, AttemptedTestCounts <=4), "Less than 5 Assessments Attempted",
                             IF(AND(AttemptedTestCounts >= 5, AttemptedTestCounts <=8), "5 to 8 Assessments Attempted",  
                             IF(AND(AttemptedTestCounts >= 9, AttemptedTestCounts <=12), "9 to 12 Assessments Attempted", 
                             IF(AND(AttemptedTestCounts >= 13, AttemptedTestCounts <=17), "13 to 17 Assessments Attempted",
                                "No Assessment Attempted"))))
                        ) 
            ) 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi @sandy2405,

 

Creating Calculated Column instead of Measure for "Test Category" to accomblish desired outcome.

 

Below are the steps to implement:-

 

1. Create New Column in you table "Test Score", name after "Test Category"

2. Apply following DAX pattern on the new column:

 

   Test Category = (
                 var AttemptedTestCounts = CALCULATE(DISTINCTCOUNT(Test Score[Test Name]), GROUPBY(Test Score,Test Score[Test Name]))
                 return (
                             IF(AND(AttemptedTestCounts >= 1, AttemptedTestCounts <=4), "Less than 5 Assessments Attempted",
                             IF(AND(AttemptedTestCounts >= 5, AttemptedTestCounts <=8), "5 to 8 Assessments Attempted",  
                             IF(AND(AttemptedTestCounts >= 9, AttemptedTestCounts <=12), "9 to 12 Assessments Attempted", 
                             IF(AND(AttemptedTestCounts >= 13, AttemptedTestCounts <=17), "13 to 17 Assessments Attempted",
                                "No Assessment Attempted"))))
                        ) 
            ) 
 
 

3. Create Measure to count rows as follows:

        

Count = COUNTA(Test Score[Test Category])

4. Drag in Participant, Test Category and Count Matrix/ Table visual.

 

 

Result.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope this helps, do let me know if I can help more.

 

Apologies, I can not attach the .pbix file as I don't see the option. 

 

Cheers

Anand Kannan

 

Hi @Anonymous

 

Can you please share your .pbix file, by sharing the link of google drive (like I shared in my question). 

Actually, I am trying your approach on my data, but i am still getting on value in Test Category (Less than 5 Assessments Attempted). I am unable to understand why?

OR, you can download my test data .pbix file form here and try your approach and see if your approach is universal or it is data specific.

Hi @sandy2405

 

You may try below formula to get the column:

Test Category = (
                 var AttemptedTestCounts = CALCULATE(DISTINCTCOUNT('Test Score'[Test Name]),ALLEXCEPT('Test Score','Test Score'[Participant identifier]))
                 return (
                             IF(AND(AttemptedTestCounts >= 1, AttemptedTestCounts <=4), "Less than 5 Assessments Attempted",
                             IF(AND(AttemptedTestCounts >= 5, AttemptedTestCounts <=8), "5 to 8 Assessments Attempted",  
                             IF(AND(AttemptedTestCounts >= 9, AttemptedTestCounts <=12), "9 to 12 Assessments Attempted", 
                             IF(AND(AttemptedTestCounts >= 13, AttemptedTestCounts <=17), "13 to 17 Assessments Attempted",
                                "No Assessment Attempted"))))
                        ) 
            ) 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @sandy2405

 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LivioLanzo
Solution Sage
Solution Sage

Hi @sandy2405

 

take a look at segmentation: https://www.daxpatterns.com/static-segmentation/

 

let me know if you';ve doubts

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

LivioLanzo
Solution Sage
Solution Sage

Hi @sandy2405

 

take a look at segmentation here:

 

https://www.daxpatterns.com/static-segmentation/

 

let me know if doubts

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.