Forum Discussion

bruzie's avatar
bruzie
Frequent Visitor
2 years ago
Solved

Expanding filter scope from slicer selected value

I have a learning summary report that I need to show not just the data belonging to a selected user, but summary data for that user's group as well.

I have the following tables:

  • Users (UserName, UserID, Group)
  • Courses (CourseID, Name, Category)
  • Assigned (CourseID, UserID, CompleteDate, Status)

These are linked in the model:

The GroupCompletions table is a summary table that rolls up course status by [user] Group and [course] Category:

GroupCompletions =
VAR CombinedTable =
    ADDCOLUMNS (
        Assigned,
        "Group", RELATED ( Users[Group] ),
        "Category", RELATED ( Courses[Category] )
    )
VAR SumTable =
    SUMMARIZE (
        CombinedTable,
        [Group],
        [Category],
        Assigned[Status],
        "Count", COUNT ( Assigned[CourseID] )
    )
RETURN SumTable

On my report I have a slicer on Users[UserName] displaying status grouped by category. In the attached sample I'm also showing the same visual using all user data, but I need to also show data for all users belonging to the same group as the selected user:

The matrix is showing data only for the selected user. The table below is showing data from the GroupCompletions table.

SelectedGroup is a measure that displays the correct group when a user is selected:

SelectedGroup =
LOOKUPVALUE ( Users[Group], Users[UserName], SELECTEDVALUE ( Users[UserName] ) )

However, I am unable to apply this value to a filter on the GroupCompletions table, as it filters out all rows:

GroupCompletions = ...[snipped for brevity]
RETURN
    FILTER ( SumTable, [Group] = [SelectedGroup] )

If I hard-code the filter value (as seen in the above screenshot), it filters as intended. I assume this is some kind of row context behaviour. I've tried searching and trying possibilities but I just can't get my head around it.

 

Any help would be very much appreciated.

 

Sample file: SamplePBIX

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi bruzie ,

    Because of the relative complexity of your report, I can't make changes directly in your report, so I can only provide you with an example in Page2 of the pbix file you provided.
    I add a new table like this:

    And there is no relationship between this table and other tables:

    Then I create a measure:

    Measure = 
VAR A = SELECTEDVALUE(Slicer[UserName])
VAR B = 
CALCULATE(
    MAX('Users'[Group]),
    FILTER(ALL(Users),
    'Users'[UserName] = A
    )
)
RETURN
IF(
    ISFILTERED(Slicer[UserName]),
    IF(
        MAX('Users'[Group]) = B,
        1,
        0
    ),
    1
)

    Make the settings according to the following figure:

    And use the table which I added before to create a slicer, and the final output is as below:

    When I select Mike, it shows Mike and everyone in Mike's group.
    You can use this as a reference to try to modify the visual objects you need to create.

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi bruzie ,

    Because of the relative complexity of your report, I can't make changes directly in your report, so I can only provide you with an example in Page2 of the pbix file you provided.
    I add a new table like this:

    And there is no relationship between this table and other tables:

    Then I create a measure:

    Measure = 
VAR A = SELECTEDVALUE(Slicer[UserName])
VAR B = 
CALCULATE(
    MAX('Users'[Group]),
    FILTER(ALL(Users),
    'Users'[UserName] = A
    )
)
RETURN
IF(
    ISFILTERED(Slicer[UserName]),
    IF(
        MAX('Users'[Group]) = B,
        1,
        0
    ),
    1
)

    Make the settings according to the following figure:

    And use the table which I added before to create a slicer, and the final output is as below:

    When I select Mike, it shows Mike and everyone in Mike's group.
    You can use this as a reference to try to modify the visual objects you need to create.

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

    • bruzie's avatar
      bruzie
      Frequent Visitor
      2 years ago

      Hi v-junyant-msft,

       

      Thanks for that. It took me a little while to get my head around it.

       

      To get it to filter on the correct data I changed the return statement on the measure:

      RETURN
IF(
    ISFILTERED(Slicer[UserName]),
    IF(
        MAX('GroupCompletions'[Group]) = [SelectedGroup],
        1,
        0
    ),
    1
)

      By extracting "B" into it's own measure I could use it for visual titles, too.

       

      Thanks heaps!

      Chris

    • bruzie's avatar
      bruzie
      Frequent Visitor
      2 years ago

      As a follow-up, I massively over-complicated the scenario. It turns out I could achieve the same thing by simply joining the Users table with the GroupCompletions summary table by the Group fields with a one-way filter, which means this will work with RLS as well.