Help with slicer/table visual and contexts

[RamEHG]

Hi Folks,

Still a newbie to PowerBi so looking for some help here. To set the context, I have 3 tables.

Table 1:

UniqueID | Name | Company | Topic

Dis1 | Ram | Comp1 | Powerbi

Dis2 | Ram | Comp1 | DAX

Dis3 | John | Comp2 | SomeTopic1

Dis4 | John | Comp2 | Some2

Dis5 | Joe | Comp3 | Blah1

Dis6 | Joe | Comp3 | Blah2

Dis6 | Per1 | Comp2 | Blah2

Table 2

Name | CompanyName | Attr1 | Attr2 | ...

Ram | Comp1 | ...

John | Comp2 | ...

Joe | Comp3 | ...

Per1 | Comp2 | ...

Per2 | Comp1 | ...

Table 3

NameNew | CompNew

Ram | Comp1

John | Comp2

Joe | Comp3

Per1 | Comp2

Per2 | Comp1

Relationships:

Table2 -> Table1 (1:* CompanyName -> Company)

Table2 -> Table3 (*:* CompanyName -> CompNew)

Heres what I'm trying to do: I have a slicer which shows the names of people (Table2). Upon selecting a name I want to be able to show a table of everyone who is at the same company as the selected person. I want a column in this table to show the count of discussions (from Table1) for each person from Table3. Example

Slicer (Table2) Selection: John

Output

Name | Company | Count of Discussions

John | Comp2 | 2

Per1 | Comp2 | 1

Slicer (Table2) Selection: Ram

Output

Name | Company | Count of Discussions

Ram | Comp1 | 2

Per2 | Comp1 | 0

I am able to get the data without the count. I tried to create a measure with the counts but everytime i add the measure to the table (visual) I get all names vs only those who are filtered through the slicer. I'm unclear as to how to be able to ensure that the filtered row context carries through to Table1 (I've tried TREATAS, INTERSECT of no avail)... Any help is appreciated..

Thanks

Ram

[RamEHG · Accepted solution]

Thanks all for the help. I was able to resolve this. I needed create another relationship between Table3 and Table1 (based on the name many-to-many). I was then able to use that relationship, the name of the person from Table3, the name of the company from Table2, ignore all the rest of the filters (ALL(Table1)) and add a set of filters which match the name and the company from above. This resulted in empty data for all names and the correct data for those who matched. I was able to filter the empty's in the visual and that did the trick.

Thanks again for all your help.

+Ram

[Anonymous]

Hi @RamEHG ,

I reviewed the post and I have a question for you.

I saw the relationship is 1:*, but in Table 2, there're 2 Comp1 and 2 Comp2 in it, therefore they can't be set up as one to manay.

If you want the expected result you mentioned, you could change your relationships as below.

Here's the result:

The slicer is created from Table 3. In the table visual, Company field, Name field and the countings are from Table 1.

You can check more details from my attachment.

Best Regards,
Stephen Tao

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

[RamEHG]

Thanks again for the response. I made a mistake in the relationships - it should be Table2 -> Table1 (1:*) (Name -> Name). Essentially I have an person table (Table2) and an meetings table (Table1). Each of these have varying attributes. I'm trying to have a slicer with a person name. Selection of a particular person should then show me all other people in the same company alongside the count of meetings each of those people have had. Hence the creation of Table3 so that I can discern "other people who worked with the selected person". And then I'm trying to get the count of meetings for each of them. It seems that I'm not able to get the row context shown from Table3 to apply to Table1 in this process.

Thanks

Thanks again

Ram

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[RamEHG]

Thanks for this. This works great with a relationship established between 2 tables. I have a slightly different issue in that I have 3 tables (albeit one is a subset of the second table just to be able to show related people in the company of the person selected).

Thanks

Ram

[DataNinja777]

Hi @RamEHG ,

To achieve the desired output, you need a measure that correctly filters the company based on the selected person and counts the discussions accordingly. Since Table2 and Table3 have a many-to-many relationship, you must ensure that the filter context propagates properly. The approach involves capturing the selected person's company, filtering Table3 based on that company, and then counting the number of discussions for each person in that company from Table1.

The following DAX measure accomplishes this:

Count of Discussions = 
VAR SelectedCompany = 
    SELECTEDVALUE(Table2[CompanyName]) 

RETURN
    CALCULATE(
        COUNT(Table1[UniqueID]),
        FILTER(
            Table3, 
            Table3[CompNew] = SelectedCompany
        )
    )

This measure works by first retrieving the company associated with the selected person in Table2 using SELECTEDVALUE(Table2[CompanyName]). Then, it applies a filter on Table3 to only include rows where the company matches the selected company. Finally, COUNT(Table1[UniqueID]) calculates the number of discussions for people within that company.

For example, when John is selected in the slicer, the table visual will display John and Per1 under Company Comp2 with discussion counts of 2 and 1, respectively. Similarly, if Ram is selected, the table will show Ram and Per2 under Company Comp1 with discussion counts of 2 and 0.

To ensure the relationships work correctly, they must be active, and the filtering logic in the measure must be applied correctly. If multiple people are selected in the slicer, SELECTEDVALUE returns BLANK(), so adjustments using VALUES(Table2[CompanyName]) may be needed for multi-selection scenarios. If Table3 does not filter properly, wrapping it with ALL(Table3) inside the FILTER function might help.

Best regards,

[RamEHG]

Thanks for the quick response. I tried it but it doesnt work. I get the info but I get all rows of Table 1. So essentially for John I get

Ram | 0

John | 2

Joe | 0

Per1 | 2

What I'm struggling with is to be able to get Table1 to filter by not just company but also the selected names in Table3