cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EF Helper I
Helper I

Tagging clients who have particular variable

Hi all,

 

I have a dataset which lists clients by appointment and which service they received.

Any client who recieved a particular service (example: service 'a') is part of a specific client population.

I need to either filter out all other clients or create a separate table of just these clients, including all the services they received, not just the 'a' that identifies them as part of this population.

 

For example:

 

account: appt_Id: Service:

111, 1, a

111, 2, b

112, 1, a

112, 2, c

113, 1, b

113, 2, c

 

service that I am looking for is 'a'.

Output I want is:

Account: Appt_ID: Service: Tagged:

111, 1, a, Yes

111, 2, b, Yes,

112, 1, a, Yes

112, 2, c, Yes

113, 1, b, No

 

So that I can filter all appointments for any clients that have ever recieved 'a' service.

113, 2, c, No

 

I tried duplicating the table, filtering other accounts and removing duplicates so that I had a distinct list of the accounts from the 'a' services population, and then using lookupvalue to tag each instance of that account back in the original dataset. However that created a circular dependancy that I can't seem to work around (pun unintended 😉

 

Any ideas?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Tagging clients who have particular variable

Hi @EF ,
Went round and round, using copy and paste from your post. Finally replaced the Service Column by hand and now things are working.
Created a new table.

 

 

Newtable = 

var _calc =CALCULATETABLE(Customer, Customer[Service] = "a")
return _calc

 

 


newtable.PNG

 

Then linked the two tables as a 1 to many.
newtable1.PNG

 



Then created a table (on the left) which gets you one of the ways you were thinking about, in that it separates out the clients.

However even better was to  create a matrix using the Account from the Newtable and the Appt ID from the Customer Table.  As there are Customers who are not in the new table (those with no a) and which create a blank, I filtered the blank. Now you have the Accounts, and all their services. 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
newtable12.PNG

 

 





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

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: Tagging clients who have particular variable

Hi @EF ,
Went round and round, using copy and paste from your post. Finally replaced the Service Column by hand and now things are working.
Created a new table.

 

 

Newtable = 

var _calc =CALCULATETABLE(Customer, Customer[Service] = "a")
return _calc

 

 


newtable.PNG

 

Then linked the two tables as a 1 to many.
newtable1.PNG

 



Then created a table (on the left) which gets you one of the ways you were thinking about, in that it separates out the clients.

However even better was to  create a matrix using the Account from the Newtable and the Appt ID from the Customer Table.  As there are Customers who are not in the new table (those with no a) and which create a blank, I filtered the blank. Now you have the Accounts, and all their services. 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
newtable12.PNG

 

 





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

Proud to be a Super User!




View solution in original post

Highlighted
EF Helper I
Helper I

Re: Tagging clients who have particular variable

Thank you Nathaniel!

 

When I created the relationship between the two tables, for some reason it didn't let me by dragging on the relationship view, but when I went into 'Manage Relationships' it worked. It was a many-to-many but it works anyway.

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors