Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |