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.
I have a table (L&L) as below which is connected to my date table:
Date Job role codes concatenated
01/11/22 123b4, 34b56, 2b343, 3a421
03/11/22 123b4, 34b56, 2b343, 3a421
3/10/22 123b4, 34b56, 2b343, 3a421
I have another table containing the codes and definitions:
123b4 Vet
34b56 nurse
3a421 manager
I want to end up with a pie chart or table showing:
Job role
Vet 3
Nurse 2
Manager 4
How do I relate these two tables and make this happen please?
@gingerclaire You can either use Power Query Editor to split that column on commas and the unpivot or you could do this:
Measure =
VAR __Code = MAX('Table2'[Code])
VAR __Table = FILTER('Table1',CONTAINSSTRING([Job role codes concatenated],__Code))
VAR __Result = COUNTROWS(__Table)
RETURN
__Result
Thank you! I used this and it is working in a table BUT it is using COUNTROWS so is giving me the wrong totals - but i think i worked out why!
One a single row there might be several instances of the same string i.e. each row is an event and there might be 5 or six vets at that event. So it needs to count how often the string/code appears.
Is there a way to do this please?
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |