Forum Discussion

lherbert501's avatar
lherbert501
Post Prodigy
3 months ago
Solved

Slicer pointing towards multiple fields

Hi ,   I have a scenario where I have two user fields within my data, with the relationship going to the User 1 field.   I have a disconnected table slicer that has the option of User1,User 2 or ...
  • johnt75's avatar
    3 months ago

    There's a few steps involved but it is certainly doable.

    Rather than using a disconnected table you could use a calculation group. Create an inactive relationship from DimUser[User] to FactDummy[User2], then create a calculation group with 3 items.

    Both = 
    VAR User1 = SELECTEDMEASURE()
    VAR User2 = CALCULATE(
        SELECTEDMEASURE(),
        USERELATIONSHIP( DimUser[User], FactDummy[User2])
    )
    VAR Result = User1 + User2
    RETURN Result
    
    User1 = SELECTEDMEASURE()
    
    User2 = 
    CALCULATE(
        SELECTEDMEASURE(),
        USERELATIONSHIP( DimUser[User], FactDummy[User2])
    )

    Add that as a single-select slicer.

    You need a measure to be able to filter on, you can use

    # Rows = COUNTROWS( FactDummy )

    Add this to your table visual, and you also need to add DimUser[User] to the table visual. Use the filter pane to set a filter on the [# Rows] measure to only show when the value is >= 1.

    If you don't want them to actually display in the table visual, turn off text wrap on the values and column headers then set the width of the column to 0.

    You could also hide the [# Rows] column in the visual by going in to the visual calculations and using the hide icon, though you don't appear to be able to do that for the DimUser[User] column.

    See the attached PBIX for a working example.