Forum Discussion

Sam_BI_Analyst's avatar
Sam_BI_Analyst
Frequent Visitor
2 years ago
Solved

Need Help with DAX for Student Details

Hello Community,

 

I need a help with DAX, I've been working on this issue from a long time. Below i have given my requirement.

 

My Data - 

 

 

Problem/Requirement  - I need details of student who have and completed only maths. I don't want students who completed maths and also have other subjects.

 

My result data should look like this- 

 

Note - I don't need student id 2 and 4 because they have maths and english as their subjects. But in case of Student 1 and 3, They only have maths and both completed maths.

 

Thanks in Advance.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Sam_BI_Analyst 

     

    Here is a measure solution. You can add this measure to a table visual as a visual-level filter and set it to show items when value is 1. 

    Flag Measure = 
var _onlySubject = CALCULATE(SELECTEDVALUE('Table (2)'[Subject]),ALLEXCEPT('Table (2)','Table (2)'[Student Id]))
RETURN
IF(_onlySubject="Maths" && SELECTEDVALUE('Table (2)'[Status])="Completed",1,BLANK())

     

    This is a column solution. You can then filter table by the new column with value 1. 

    Column = 
var _onlySubject = CALCULATE(SELECTEDVALUE('Table (2)'[Subject]),ALLEXCEPT('Table (2)','Table (2)'[Student Id]))
return
IF(_onlySubject="Maths" && 'Table (2)'[Status]="Completed",1)

     

    Best Regards,
    Jing

7 Replies

  • VN999's avatar
    VN999
    Icon for Resolver I rankResolver I
    2 years ago

    Create Measure 1 :

    This measure counts the number of rows where the subject is "Maths" and the status is "Completed".

    CompletedMaths =
    CALCULATE(
    COUNTROWS('Enrollment'),
    FILTER(
    'Enrollment',
    'Enrollment'[Subject] = "Maths" && 'Enrollment'[Status] = "Completed"
    )

     

    Create Measure 2:

    This measure counts the number of rows where the subject is "English" and the status is "In Progress"

    InProgressEnglish =
    CALCULATE(
    COUNTROWS('Enrollment'),
    FILTER(
    'Enrollment',
    'Enrollment'[Subject] = "English" && 'Enrollment'[Status] = "In Progress"
    )
    )

     

    Combine both 1&2 Filters:

    This filter condition retrieves rows where the subject is "Maths" and status is "Completed", or where the subject is "English" and status is "In Progress".

    StudentsDetails =
    FILTER(
    'Enrollment',
    'Enrollment'[Subject] = "Maths" && 'Enrollment'[Status] = "Completed" ||
    'Enrollment'[Subject] = "English" && 'Enrollment'[Status] = "In Progress"
    )


    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Sam_BI_Analyst 

     

    If you want to have the outcome in a new table, you can try this 

    Table 2 = FILTER('Table',('Table'[Suject]="Maths"&&'Table'[Status]="Completed")||('Table'[Suject]="English"&&'Table'[Status]="In Progress"))

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

    • Sam_BI_Analyst's avatar
      Sam_BI_Analyst
      Frequent Visitor
      2 years ago

      Hello Anonymous ,

       

      Thanks for your response, I really appreciate your efforts. My requirements got changed. Can you please look into the new requirements and suggest a solution.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Sam_BI_Analyst 

        For the new requirement, you can try this 

        Table 3 = 
var _table = SELECTCOLUMNS(FILTER(SUMMARIZE('Table (2)','Table (2)'[Student Id],"Only_Has_Maths",SELECTEDVALUE('Table (2)'[Subject])="Maths"),[Only_Has_Maths]=TRUE()),"Student_Id",'Table (2)'[Student Id])
return
FILTER('Table (2)','Table (2)'[Student Id] IN _table)

        If you only want the Student Ids, you can return _table only. 

         

        Best Regards,
        Jing
        If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!