Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi there,
I have a database with a unique user ID, a customer ID and a column showing the user is active.
User ID | Customer ID | User is Active |
1 | 1 | FALSE |
2 | 1 | FALSE |
3 | 2 | FALSE |
4 | 2 | TRUE |
5 | 3 | TRUE |
6 | 3 | TRUE |
7 | 4 | FALSE |
8 | 4 | FALSE |
9 | 5 | TRUE |
10 | 5 | FALSE |
I now want to have a column that's showing me on a customer level whether they have any active users. In Excel I would do this with a countIF function but I can't seem to find out how to do this in PowerBI. Here's an example if what I would be looking for:
User ID | Customer ID | User is Active | Customer is active |
1 | 1 | FALSE | FALSE |
2 | 1 | FALSE | FALSE |
3 | 2 | FALSE | TRUE |
4 | 2 | TRUE | TRUE |
5 | 3 | TRUE | TRUE |
6 | 3 | TRUE | TRUE |
7 | 4 | FALSE | FALSE |
8 | 4 | FALSE | FALSE |
9 | 5 | TRUE | TRUE |
10 | 5 | FALSE | TRUE |
Solved! Go to Solution.
Could you please add a new column in "Data" pane as below:
CustomerActive = VAR _TrueVal = SELECTCOLUMNS(FILTER(ALL(UserCustomer),UserCustomer[UserActive]="True" && UserCustomer[CustomerID] = EARLIER([CustomerID])),"UserActive",UserCustomer[UserActive]) RETURN IF(ISBLANK(_TrueVal),"FALSE",_TrueVal)
Here is the expected output as per the sample data provided...
Proud to be a PBI Community Champion
@Anonymous,
Can you share more details about what you are looking for?
Sorry I didn't get you.
How will you decide whether a user or a customer is active?
Hi Siva,
Thanks for helping out. Sorry for not being clear on this.
A Customer can be seen as a separate company. Every customer/company has multiple users. Those users can be active or inactive.
I have a dataset that tells me for every user whether they are active or not, and what customer they are working for.
What I now want to know for each customer is whether these customers have at least 1 active user or not.
Cheers
Bas
Could you please add a new column in "Data" pane as below:
CustomerActive = VAR _TrueVal = SELECTCOLUMNS(FILTER(ALL(UserCustomer),UserCustomer[UserActive]="True" && UserCustomer[CustomerID] = EARLIER([CustomerID])),"UserActive",UserCustomer[UserActive]) RETURN IF(ISBLANK(_TrueVal),"FALSE",_TrueVal)
Here is the expected output as per the sample data provided...
Proud to be a PBI Community Champion
Thanks! I've tried it out but I'm getting an error on the EARLIER function.
In my example I gave the CustomerID and the UserID a number, but in fact they are text based. What should I replace the EARLIER function with?
Please post tthe screenshot of the error you are facing...
Proud to be a PBI Community Champion
Hi @Anonymous
You may try to change the data type to 'Text' for column 'UserActive'.
Regards,
Cherie
That did the trick! Thanks so much for helping out!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |