Tagging clients who have particular variable

[EF]

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?

[Nathaniel_C · Accepted solution]

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

Then linked the two tables as a 1 to many.

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

[EF]

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.