Dax Help

[venujasti]

Hi,

 We are using SSAS tabular model as a data source for PowerBI. I have the below scenario and i am trying to write dax formule for it. Any help is much appreciated

• How to calculate or summarize on two tables which are not related

                      Example

We have two tables(Students,enrollment) which does not have a direct relationship. ‘Student’ table should be filtered on a condition where student age should be greater than X years . ‘Enrollment’ table has three conditions on different columns. I need to get distinct ‘studentid’ from enrollment(as each student have multiple enrollments) and pass it ‘Student’ table and calculate the count of records matching conditions from both tables.

• How to calculate or summarize on two tables which are related

                      Example

We have two tables(Students,Services) which does have a direct relationship. The tables are connected on studentid column .‘Student’ table should be filtered on a condition where status code is 1 or 2 in a column . I need to calculate count of distinct students with filtered students from prior conditions(students table) does not have some particular services(eg: service number 100,200). Each student will have multiple services

Student Table

StudentId	Student Birth date	Status code

Service Table

Sno	StudentId	Service number

Enrollment Table

Enrollment Number	StudentId	Column 1	Column2	Column 3

Thanks

[Greg_Deckler]

Check out my blog post here:

http://community.powerbi.com/t5/Community-Blog/Good-Ol-VLOOKUP-The-Ultimate-Guide-to-Lookups-in-Powe...

[venujasti]

Hi,

 We are using SSAS tabular model as a data source for PowerBI. I have the below scenario and i am trying to write dax formule for it. Any help is much appreciated

• How to calculate or summarize on two tables which are not related

                      Example

We have two tables(Students,enrollment) which does not have a direct relationship. ‘Student’ table should be filtered on a condition where student age should be greater than X years . ‘Enrollment’ table has three conditions on different columns. I need to get distinct ‘studentid’ from enrollment(as each student have multiple enrollments) and pass it ‘Student’ table and calculate the count of records matching conditions from both tables.

• How to calculate or summarize on two tables which are related

                      Example

We have two tables(Students,Services) which does have a direct relationship. The tables are connected on studentid column .‘Student’ table should be filtered on a condition where status code is 1 or 2 in a column . I need to calculate count of distinct students with filtered students from prior conditions(students table) does not have some particular services(eg: service number 100,200). Each student will have multiple services

Student Table

StudentId	Student Birth date	Status code

Service Table

Sno	StudentId	Service number

Enrollment Table

Enrollment Number	StudentId	Column 1	Column2	Column 3

Thanks

[Greg_Deckler]

Check out my blog post here:

http://community.powerbi.com/t5/Community-Blog/Good-Ol-VLOOKUP-The-Ultimate-Guide-to-Lookups-in-Powe...

[venujasti]

Sorry could not reply sooner as i was on vacation

Thanks for the reply. I will follow the link and try to implement.

Venu Jasti 

[v-sihou-msft · Accepted solution]

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

[venujasti]

Thanks , the solution worked for me