True/False DAX query

[jpbi]

I have 3 three tables (see image at bottom of question) which represent a list of students belonging to a developer community, the universities they attend and if they have a PHD or not (0 if yes, 1 if no).

What I want to do is produce a report that has a list of universities and TRUE or FALSE depending on whether they have any PHD students or not. So, in this example snippet, the output report would be two universities - "Zurich" - "TRUE", "Edinburgh" "FALSE".

What query could I use to generate the values for this PHD true/false column? The issue i'm having is I don't know how to write such a query to cover 3 tables. Help would be much appreciated.

[judspud · Accepted solution]

Hi @jpbi 

As long as your tables are all connected properly the mesaure should span across them with no problems.

Im assuming your tables follow the relationships below

Universities to StudentUniversities on the University column

StudentUniversities to Students on the Student column

If so, then a measure as below should work:

 if(sum(Students[phd])>0,True(),False)

Place into a table the name column from the Universities table and the measure and you should have the result you requested 🙂

Hope this helps

Thanks,

George

[parry2k]

@jpbi few question before post the solution:

in student univesity table, does each student can be only in one university or multiple university?

in student table, I assume each student is a unique row.

[jpbi]

---

@parry2k wrote:

@jpbifew question before post the solution:

in student univesity table, does each student can be only in one university or multiple university?

in student table, I assume each student is a unique row.

---

A student can only be part of one university.

And yes correct, each student is unique row 

[parry2k]

@jpbi solution proposed by @judspud will work.

[jpbi]

Thanks for your help @judspud @parry2k