Summarize a column from another table while using a slicer

[Syndicate_Admin]

Hello! I have two tables with the following, in a one-to-many relationship:

Data

Id	Value
Off	$1
Xy	$2
Cz	$3

Activities

Id	Number	State
Off	Bobby	Ongoing
Xy	Billy	Full
Off	Bobby	Full
Xy	Benny	Ongoing
Off	Billy	Waiting
Cz	Benny	Full

In the report I have a slicer to select a name and a data table, where I want to show the progress of the activities for the selected name. This should be summarized, so show In progress if something is in progress, if not show Not Started if there are any, if not showing waiting if there are any, if not showing full if any, otherwise leave it blank. Ideally, this will be the result:

[Billy selected in Name slicer]
Data

Id	Value	State
Off	$1	Waiting
Xy	$2	Full

[Bobby selected in Name slicer]
Data

Id	Value	State
Off	$1	Ongoing

[Benny selected in Name slicer]
Data

Id	Value	State
Xy	$2	Ongoing
Cz	$3	Full

There are many more names and IDs, but only a few states. How do I configure this?

[v-alq-msft · Accepted solution]

Hi, @Syndicate_Admin 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Activities:

Data:

You may create two measures  as below.

Result Value = 
SUMX(
    ADDCOLUMNS(
        Activities,
        "Result",
        LOOKUPVALUE(Data[Value],Data[Id],[Id])
    ),
    [Result]
)

Result State = 
CONCATENATEX(
    SUMMARIZE(
        Activities,
        Activities[Id],
        "Result",
        CONCATENATEX(
            Activities,
            [State],
            ","
        )
    ),
    [Result],
    ","
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Assume you have the following tables...

Table Name : Data

Table Name: Activities

Create two measures...

Value Measure = 
VAR CurrentID = SELECTEDVALUE(Activities[Id])
VAR Result = LOOKUPVALUE(Data[Value],Data[Id],CurrentID)
RETURN Result

State Measure = 
VAR States = VALUES(Activities[State])
VAR Result =
    SWITCH(
        TRUE(),
        "Ongoing" IN States,"On Going",
        "Not Started" in States, "Not Started",
        "Waiting" in States, "Waiting",
        "Full" in States, "Full",
        BLANK()
    )
RETURN Result

Add a matrix visual to your report and add the field Activites[Id] to the Rows and add the two measures created above to the values section.

Also, add a slicer with the Activities[Number] field. 

This will give the following output...