Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
I have dataset with below columns:
I achieved the required output in SQL server, using below query:
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:
Please help me here on below points:
Thanks in Advance,
Sandy
Solved! Go to 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
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.
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
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
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!
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |