Hi! I have two tables with the following, in a one-to-many relationship:
Data
ID | Value |
AB | $1 |
XY | $2 |
CZ | $3 |
Activities
ID | Name | Status |
AB | Bobby | In Progress |
XY | Billy | Complete |
AB | Bobby | Complete |
XY | Benny | In Progress |
AB | Billy | Waiting |
CZ | Benny | Complete |
In the report I have a slicer to select a Name and a table of Data, where I want to show the progress of activities for the selected Name. This should be summarised, so show In Progress if there is anything in progress, if not show Not Started if there is any, if not show Waiting if there is any, if not show Complete if there is any, else leave it blank. Ideally, this will be the result:
[Billy selected in Name slicer]
Data
ID | Value | Status |
AB | $1 | Waiting |
XY | $2 | Complete |
[Bobby selected in Name slicer]
Data
ID | Value | Status |
AB | $1 | In Progress |
[Benny selected in Name slicer]
Data
ID | Value | Status |
XY | $2 | In Progress |
CZ | $3 | Complete |
There are many more names and IDs, but only a few Statuses. How can this be set up?
Solved! Go to Solution.
Hi, @Alex_
Accord to your description, I think you can create a measure to display in table visual.
Like this:
Measure =
VAR a =
VALUES ( Activities[Status] )
RETURN
IF (
"In Progress" IN a,
"In Progress",
IF ( "Waiting" IN a, "Waiting", IF ( "Complete" IN a, "Complete" ) )
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Alex_
Accord to your description, I think you can create a measure to display in table visual.
Like this:
Measure =
VAR a =
VALUES ( Activities[Status] )
RETURN
IF (
"In Progress" IN a,
"In Progress",
IF ( "Waiting" IN a, "Waiting", IF ( "Complete" IN a, "Complete" ) )
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
119 | |
75 | |
66 | |
51 | |
49 |
User | Count |
---|---|
183 | |
101 | |
80 | |
79 | |
77 |