@WonderLaura

This one is going to be a bit different than a lot of what I usually write, but I learned something new today, and wanted to share some of the steps with you.  I worked in Power BI in Office 365 today, and learned a little DAX, which stands for Data Analysis Expressions.

Here’s the challenge.  I have a list of people, and a list of classes that people have taken, and I need to obtain a list of the classes that people have NOT taken.  There are three classes, so I just need something like this:

Employee Email	Class 1	Class 2	Class 3
Bob	Yes		Yes
Sally	Yes
Sue		Yes

 

It wasn’t too bad trying to figure out what the query would look like.  The challenge is to do it in SharePoint Online with Office 365.  The easy way for me was to go into good old, comfortable MS Access, and whip up a couple of queries.  Since you can pull in any SharePoint data as a table in Access, this only took me about 10 minutes, and there was the needed output.  Unfortunately, this isn’t a viable solution.  Most people don’t have MS Access installed, and they would need to have it in order to open up this query whenever they need the report.

My customer is on Office 365, and they do have Power BI.  So, I decided to just re-create the queries that I had done in MS Access… in Power BI.  What a challenging afternoon!

1. I downloaded and installed the free Power BI Desktop app, and clicked Get Data. I chose SharePoint Online List and clicked Connect.
   

 

• Then, when asked to authenticate, I chose my Organizational account, and signed in.
• From here, I simply entered in the URL of my site, and it gave me all of the lists and libraries to choose from. With SharePoint data, I don’t recommend selecting multiple lists at once here, because you’ll inherently get an error that both lists contain a column called “ID”, which is a conflict.  This is just a screenshot from some other site of mine with travel requests in it.  Click Edit.
  
• This next screen is where you can clean up your data.  I recommend reading this detailed information about the Query Screen.  You can click column headings and delete them that way, or click the Choose Columns button and uncheck the ones you don’t really need for your business intelligence purposes.  If you need the ID field for lookup references, etc, then leave it in there.  Just keep in mind that when you bring in another SharePoint list, it will give an error if both lists have an ID column, so you may want to either remove or rename the one in the current list.  The same probably goes for some of the other common columns that you’ll see across lists, like Created By, etc.  I cleaned most of those out, so I didn’t have any issues.
  
• Okay you’ll probably want to bring in another list or two, or even data from places other than SharePoint.  You can quickly click the Recent Sources button in the ribbon, to go straight to that site you just had opened, to grab another list. You won’t have the Edit button like at step 4, just select your list and click OK.
• When I was done pulling in lists, I clicked Close and Apply at the top left.
  
• Then, I figured I needed to create the relationship between the two tables, because Power BI isn’t going to automatically know that there’s a lookup field between the two, like SharePoint would.  It doesn’t have to be a lookup field, either.  It can just be two fields that have matching data in them.  I clicked on the relationship manager (that little icon on the far left with three squares), to get here.  Then, I dragged the Trip Type field over to the Title field in the Trip Types list, since I know the data in those two fields matches.  Then, I double-clicked on that little yellow line between the two, which brought up this Edit Relationship screen.  Looks good.
  

 

Okay, so that’s what some of those screens look like.  I just didn’t want to use customer data in my screenshots, so I used a test list in my own site, and I don’t really have time to re-create their whole structure in my test environment. Sorry about the possible confusion between travel requests and classes example.

So…

That was the easy part.  Then it was time to figure out how to create the report.  I pulled in the list of people (“People”), and the list of classes (“Training”) that they had taken.  The common field is the person’s email address.

In MS Access, I had created 3 different queries, one for each class, just listing the people who had taken it.  Then, for the big report, I had “Yes” in the class column if an item existed for that employee in each of the 3 classes.  This is what the SQL statement looks like:

SELECT [People].Email, [People].FirstName, [People].LastName, IIf(IsNull([Took Class 1]![Email]),””,”Yes”) AS Class1, IIf(IsNull([Took Class 2]![Email]),””,”Yes”) AS Class2, IIf(IsNull([Took Class 3]![Email]),””,”Yes”) AS Class3

FROM (([People] LEFT JOIN [Took Class 1] ON [People].Email = [Took Class 1].Title) LEFT JOIN [Took Class 3] ON [People].Email = [Took Class 3].Title) LEFT JOIN [Took Class 2] ON [People].Email = [Took Class 2].Title

ORDER BY [People].LastName;

Anyway, I don’t know anything about SQL, and I don’t know if that was even the most efficient way to do it (done in Access), but I do know that I then needed to translate this somehow to DAX, which is the query language that Power BI uses, so that I could make this into a Power BI report.

After a lot of research and figurin’ out, here’s what I came up with, and it seems to work.

1. I went to the Modeling tab in Power BI, and clicked New Table, which is kinda like creating a new query in Access.
2. This is all I typed, to get my first list of just the people who have taken class 1.
   Took Class1 = filter(‘Training’,’Training'[Class_LookupId]=”1″)
   That lookupID is a lookup field in SharePoint that looks up to the name of the class.  The ID of the first class happens to be 1, and Power BI sees it as a string.
3. Voila! Instant list of people who took that class! Yes, I’m a nerd, I thought it was cool.  Anyway, then I just copied that formula and did steps 1 and 2 here, two more times for the other classes as two more tables, like this:
   Took Class2 = filter(‘Training’,’Training'[Class_LookupId]=”2″)
   Took Class3 = filter(‘Training’,’Training'[Class_LookupId]=”3″)
4. Then, instead of creating a whole new query for the final result like I had done in Access, I just added three calculated columns to my “People” table in Power BI.  I opened the People table, clicked the Modeling tab at the top, and clicked New Column.
5. For the Class 1 column, here’s what I did for the calculation:
   Class1 = if(ISBLANK(RELATED(‘Took Class1′[Title])),””,”Yes”)
6. Then, I created two more calculated columns, with the following formulas:
   Class2 = if(ISBLANK(RELATED(‘Took Class2′[Title])),””,”Yes”)
   Class3 = if(ISBLANK(RELATED(‘Took Class3′[Title])),””,”Yes”)

End result?  Well yes of course, it looks just like the table I drew at the top of this post. For the RELATED part, it knows about the relationship between tables because of the relationships that I was sure to establish in the relationship manager at step 7.  Basically, if there is not a matching item in the other table, it shows “” blank, otherwise it says “yes”.  IT SEEMS SO SIMPLE! Those formulas are so short and simple, so I can see why this is the new query language.  It seems to be more efficient.  I still don’t know what the hell I’m doing, but I managed to figure out those few formulas.  How did I figure it out?  The whole reason I wrote this post, is to share with you all of the resources I used, and also for next time I’m trying to remember how I did this. 

 

DAX basics in Power BI Desktop

Calculated tables in Power BI Desktop

DAX Function Reference

DAX Resource Center

DAX: Calculated Columns vs. Measures (Video)

Context in DAX Formulas

DAX Query Basics – This one is AWESOME.  He walks you through a bunch of examples of SQL and how each one translates to DAX.

Power BI Samples

 

OH yeah, and then when I tried to publish my Power BI dashboard to an Office 365 group, I got an error:
Need Power BI Pro for Groups.  To access groups in Power BI, try Power BI Pro for 60 days, or contact your admin… blah blah.

So then I found this licensing model.

https://powerbi.microsoft.com/en-us/pricing/

I guess the next step is to go PRO.  In the meantime, I sent my customer a screenshot of the cool dashboard I created.