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, U...
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.

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