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 both and then a slicer with the user name.

 

I want to be able to select which field the user name slicer is looking at on the image below, and then this determines which field it points towards, bearing in mind the current relationship is user 1. 

 

 

 

I also want to display the data in a table and not as a measure, so I'm thinking a visual filter may be best.

 

I have attached my dummy sample here:  Dummy Multiple User Selection.pbix

 

Is anybody able to please help with getting this to work?

 

Thanks

 

 

  • 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.

     

4 Replies

  • 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.

     

  • Hello,

    I’m not completely sure, but I think this is usually done with a disconnected slicer plus a DAX measure or calculated filter logic using SWITCH/SELECTEDVALUE to decide whether to filter User1, User2 or both

    Since you want it in a table and not only as a measure,you can try applying the logic as a visual-level filter with a helper measure returning 1/0, alternatively you could look into using inactive relationships with USERELATIONSHIP if the model allows it

    Best regards,
    Daniele

  • v-hjannapu's avatar
    v-hjannapu
    Community Support

    Hi lherbert501,

    I would also take a moment to thank johnt75  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

    Regards,
    Community Support Team.