Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filtering Mixed Groups

I've got data roughly as below:

 

Part NumberSystemPressureSize
A1929Horizontal1000bar5mm
A2910Horizontal + Vertical1000bar7mm
A2819Horizontal1500bar5mm+7mm
A2839Vertical1000bar + 1500bar

5mm

A5463Horizontal + Vertical1500bar

7mm

A4563Horizontal1000bar + 1500bar

5mm + 7mm

 

I would like to create filters on the columns that would mean if I wanted to filter, for example, 'Horizontal', it would filter to lines 1, 2 and 3.  Same for the other two columns.  And also, then, I'd be able to build a fully filtered selection, such as 'Horizontal/1500bar/5mm'.  I feel like there must be a simple way of doing this but struggling.  I'd like to end with a dashboard that people can select their system and see the relevant part numbers filtered.

 

Thanks,

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

    You can create three calculated table as follows.

     

    PressureSlicer = DISTINCT('Table'[Pressure])
SizeSlicer = DISTINCT('Table'[Size])
SystemSlicer = DISTINCT('Table'[System])

     

     

    Then you may create three measures as follows.

     

    ContainPressure = 
IF (
    SUMX (
        FILTERS ( 'PressureSlicer'[Pressure] ),
        IF (
            CONTAINSSTRING ( SELECTEDVALUE ( 'Table'[Pressure] ), [Pressure] ),
            1,
            BLANK ()
        )
    ) <> 0,
    1,
    0
)

ContainSize = 
IF(
    SUMX(
        FILTERS(
            SizeSlicer[Size]
        ),
        IF(
            CONTAINSSTRING(SELECTEDVALUE('Table'[Size]),[Size]),
            1,
            BLANK()
        )
    )<>0,
    1,
    0
    
)

ContainsSystem = 
IF(
    SUMX(
        FILTERS(
            SystemSlicer[System]),
            IF(
                CONTAINSSTRING(SELECTEDVALUE('Table'[System]),[System]),
                1,
                BLANK()
            )
    )<>0,
    1,
    0
    
)

     

     

    Finally, you may put these measures in visual level filters and configure as follows.

     

    Result:

     

    Best Regards

    Allan

     

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

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    You can put fields 'System', 'Pressure', and 'Size' into slicer visuals, put the your desired fields into Table visual. When you make some selections in slicers, the table visual will return corresponding result. If you need the slicer works on the dashboard in Power BI service, you might click 'Pin a live Page' to pin the current page to the dashboard.

     

    If I misunderstand your thoughts, please inform me of your expected output. I am glad to solve the problem for you.

     

    Best Regards,

     

    Allan

     

    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
      6 years ago

      Thanks Allan.  I maybe wasn't clear enough.  What I'm trying to do is have a simplified filters, so that there is just one button to click for '1000bar' for example, which would filter '1000bar' and '1000bar + 1500bar'.  And one buttong for '1500bar', which would filter '1000bar + 1500bar' and '1500bar'.  The data i have is more complex than the example I've shown, so not simplifying it this way would make filtering the standard way, as you've shown, far more complicated and time consuming.

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, Anonymous 

        You can create three calculated table as follows.

         

        PressureSlicer = DISTINCT('Table'[Pressure])
SizeSlicer = DISTINCT('Table'[Size])
SystemSlicer = DISTINCT('Table'[System])

         

         

        Then you may create three measures as follows.

         

        ContainPressure = 
IF (
    SUMX (
        FILTERS ( 'PressureSlicer'[Pressure] ),
        IF (
            CONTAINSSTRING ( SELECTEDVALUE ( 'Table'[Pressure] ), [Pressure] ),
            1,
            BLANK ()
        )
    ) <> 0,
    1,
    0
)

ContainSize = 
IF(
    SUMX(
        FILTERS(
            SizeSlicer[Size]
        ),
        IF(
            CONTAINSSTRING(SELECTEDVALUE('Table'[Size]),[Size]),
            1,
            BLANK()
        )
    )<>0,
    1,
    0
    
)

ContainsSystem = 
IF(
    SUMX(
        FILTERS(
            SystemSlicer[System]),
            IF(
                CONTAINSSTRING(SELECTEDVALUE('Table'[System]),[System]),
                1,
                BLANK()
            )
    )<>0,
    1,
    0
    
)

         

         

        Finally, you may put these measures in visual level filters and configure as follows.

         

        Result:

         

        Best Regards

        Allan

         

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