When any row in a column equals True, return True

[Anonymous]

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

[SivaMani]

@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?

[Anonymous]

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

[PattemManohar · Accepted 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...

Output

[Anonymous]

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?

[PattemManohar]

Please post tthe screenshot of the error you are facing...

[Anonymous]

I had to change "true" to true() because its a boolean columnBut when doing that I get this 'expressions that yield variant data-type cannot be used to define calculated columns

[v-cherch-msft]

Hi @Anonymous

You may try to change the data type to 'Text' for column 'UserActive'.

Regards,

Cherie

[Anonymous]

That did the trick! Thanks so much for helping out!