Filtering Mixed Groups

[Anonymous]

I've got data roughly as below:

Part Number	System	Pressure	Size
A1929	Horizontal	1000bar	5mm
A2910	Horizontal + Vertical	1000bar	7mm
A2819	Horizontal	1500bar	5mm+7mm
A2839	Vertical	1000bar + 1500bar	5mm
A5463	Horizontal + Vertical	1500bar	7mm
A4563	Horizontal	1000bar + 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,

[v-alq-msft]

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]

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 · Accepted solution]

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.