Dax Help

venujasti

 

When two tables (Student, Enrollment) are not related, we can use FILTER() in calculate relate tables in DAX without using relationships. See: How to relate tables in DAX without using relationships. Please refer to following steps:

 

1. Create a calculated column for student age in Student table.
   

   Student Age = 
   ROUNDDOWN ( ( TODAY () - Student[Student Birth date] ) / 365, 0 )
   

2. I assume the three condition columns in ‘Enrollment’ table are like below. And we want to get students whose age is greater than 23 and they have “A” in all of the three columns.
   
   Create a calculate column in Student table, which will show the number of enrollment for each student who matches conditions from both tables. No number will return if the student does not match the conditions.
   

   Distinct_ID_Age_Conditions = 
   IF (
       Student[Student Age] > 23,
       CALCULATE (
           COUNT ( Enrollment[StudentId] ),
           FILTER (
               Enrollment,
               Enrollment[Column 1] = "A"
                   && Enrollment[Column 2] = "A"
                   && Enrollment[Column 3] = "A"
                   && Student[StudentId] = Enrollment[StudentId]
           )
       ),
       BLANK ()
   )
   

   
3. Create a measure to get the distinct ‘studentid’.
   

   Count_Of_Records = 
   COUNTROWS ( Student ) - COUNTBLANK ( Student[Distinct_ID_Age_Conditions] )
   

   

When two tables (Student, Service) are related, we can use RELATED() function to calculate. Please refer to following steps.

1. Create a calculated column which will mark the students who do not have some particular services(e.g. service number 100,200).
   

   Have_Particular_Services = 
   IF (
       CALCULATE (
           COUNTROWS ( Service ),
           Service[Service number] = 100
               || Service[Service number] = 200,
           ALLEXCEPT ( Service, Service[StudentId] )
       )
           > 0,
       TRUE (),
       FALSE ()
   )
   

   
2. Create a measure to get the distinct ‘studentid’, for whose status code is 1 or 2 and particular services is not 100 or 200.
   

   Distinct_ID_No_Ser_100_200 = 
   CALCULATE (
       DISTINCTCOUNT ( Service[StudentId] ),
       Service[Have_Particular_Services] = FALSE (),
       ALLEXCEPT ( Service, Service[StudentId] ),
       FILTER (
           Service,
           RELATED ( Student[Status code] ) = 1
               || RELATED ( Student[Status code] ) = 2
       )
   )
   

   

I have also attached the .pbix file.