Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

When any row in a column equals True, return True

Hi there,

I have a database with a unique user ID, a customer ID and a column showing the user is active. 

 

User IDCustomer IDUser is Active
11FALSE
21FALSE
32FALSE
42TRUE
53TRUE
63TRUE
74FALSE
84FALSE
95TRUE
105FALSE

 

 

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 IDCustomer IDUser is ActiveCustomer is active
11FALSEFALSE
21FALSEFALSE
32FALSETRUE
42TRUETRUE
53TRUETRUE
63TRUETRUE
74FALSEFALSE
84FALSEFALSE
95TRUETRUE
105FALSETRUE

 

 

1 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...

 

OutputOutput





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

8 REPLIES 8
SivaMani
Resident Rockstar
Resident Rockstar

@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
Not applicable

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...

 

OutputOutput





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

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...





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

I had to change "true" to true() because its a boolean columnI 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 columnsBut when doing that I get this 'expressions that yield variant data-type cannot be used to define calculated columns

Hi @Anonymous

 

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

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.