March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
90 | |
81 | |
49 |
User | Count |
---|---|
160 | |
145 | |
102 | |
72 | |
55 |