Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |