“Who did it?”
These type of questions always come. “How many of my users did this?”, “On how many days did we do that” or simply “How many of my users where active?”. Instead of knowing how much each user did of what, we eventually tend to want to know how many users did what.
I feel like this is one of those super power calculations that most people out there will have a lot of practical use cases for and I recently solved it in a new way. That gave me some inspiration and here I sit again to write a blog post and share my new insight with you! So let’s dig in!
If you’re like me and prefer to test it out yourself, feel free to download this sample report from github and play around!
Why is this even an issue? Just give me the data!
The issue is simple. Your model should look something like this, where your dimensions have a one-to-many relationship going one way, not both. Notice the small arrows on the lines indicating that the Calendar is allowed to filter down the Usage-table but not the other way around. The same goes for the User table.
This is good since we can add many more tables and still make use of just one master table of the dates and one master table of the users. If we made the relationship go both ways, we would soon end up with circular dependencies.
With this model I can select my dimensions and filter out my facts. For example I can filter on Alec Harmon and filter out only those rows of the Usage table that belongs to him.
The issue is that we currently want this the other way around. We want to, for example, see how many of our users have been active on Power BI. So how can this be done without changing the entire model and risking destroying the entire report?
Introducing Crossfilter in a measure!
Behold the magic! This is the measure without crossfiltering the tables.
Stating the UsedPowerBI in Usage needs to be true has no power here. Every day shows every user.
Now witness as we add the magic sprinkle of Crossfilter. Not so tough no more, ey?
OK so what we’re currently seeing is how many users where actively using Power BI according to our usage table in the period. All 20 users where active some time during January and on a day to day basis almost everyone was active.
Why is this??
Well the crossfilter function will, for the specific calculation inside the measure, recreate the data model. This recreation is only applied in that calculation and so it doesn’t break the rest of your model.
We can use Crossfilter in Calculate to specify the direction of a relationship in our model. It can go both ways or you can specify that one filters the other (or vice versa). You can even set it to none, rendering the relationship inactive!
If you edit the relationship, you can see “Crossfilter” down on the right corner. The actual model it set to Single (left) but in our measure, we specify it to be both (as if it was configured like on the right side).
Another way of seeing it is like this. The model is actually configured like on the left side, but for this measure specifically it acts like on the right side.
Let’s kick it up a notch
OK so let’s say we want to go bananas on that Usage-table! Let’s define an active user as someone that has used Power BI, made more than 3 Teams calls and ignored 1-3 emails in a single day.
We get 6 unique users matching our criteria!
Now hold on.. Somethings weird here, right?
Well no. We are looking at amount of users matching the criteria and the same user can be counted more than once if they have more than 1 day matching, but they’re still only one user and on the total they will still only be counted once. Neat!
What if we do the same for another dimension, like the calendar? Let’s make a measure where we count calendar rows (i.e. days) and make the relationship between usage and calendar go both ways.
Putting this measure over the dimension of our users, we can now see that on a total there were 24 days where someone was actively using Power BI and then the amount of days each person was using it.
If we add the measure to a calendar table on the canvas and select a user, we can actually see the exact days that user was active in Power BI by simply selecting the user from another table.
OK let’s make another measure to filter out a portion of our users. These are the crazy users (for reasons unknown). In our User table, I have more meta data on the user, such as company name. In the calendar I have week days. Using this method I can quickly show not only how many users falls under the criteria in the measure by weekday but also divided by company!
Have any follow up questions? Reach out in the comments or connect with me on Linkedin!
Cheers!
About The Author
