Filtering on Multiple Columns

DAX DAX Calculations
1

Hi
I am to author a report that has a few tables in the model with relationships intact. The problem is that the column used in the relationship is also pivoted in the report. This same column is used in the slicer to filter the report. The requirement is that when you choose a field in the slicer, it should filter the pivoted columns to return that code or in another word, find the matching code in the pivoted columns within the date range.

image
image2177Ă—1202 272 KB

As seen from the image above, columns Process Code 1 to Process Code 6 are pivoted from column Process code. The idea is that when a user for example filters by “Pang”, every pivoted column that has this code within the date range should display it in the report. How do I accomplish this task, please?
My model is attached.
Process Code Model.pbix (70.5 KB)

2

Hi @upwardD,

Don’t know of a more elegant way to achieve this but maybe this will do.

I created a disconnected table and placed that field in the slicer on your page

Disconnected Process code = 
VALUES( 'Process Code'[Process Code] )

Next I created this measure and placed that in the “Filter on this Visual” section of the table.

Filter = 
VAR SelValue = VALUES( 'Disconnected Process code'[Process Code] )
VAR TestValue = 
SELECTCOLUMNS(
    FILTER(
        UNION(
                SELECTCOLUMNS(
                'Process Details',
                    "Record", [Record],
                    "Date", [StartDate],
                    "Process code", [Process Code1]
                ),
                SELECTCOLUMNS(
                'Process Details',
                    "Record", [Record],
                    "Date", [StartDate],
                    "Process code", [Process Code 2]
                ),
                SELECTCOLUMNS(
                'Process Details',
                    "Record", [Record],
                    "Date", [StartDate],
                    "Process code", [Process Code 3]
                ),
                SELECTCOLUMNS(
                'Process Details',
                    "Record", [Record],
                    "Date", [StartDate],
                    "Process code", [Process Code 4]
                ),
                SELECTCOLUMNS(
                'Process Details',
                    "Record", [Record],
                    "Date", [StartDate],
                    "Process code", [Process Code 5]
                ),
                SELECTCOLUMNS(
                'Process Details',
                    "Record", [Record],
                    "Date", [StartDate],
                    "Process code", [Process Code 6]
                )
            ),
            [Process code] <> ""
    ),  "@Process Code", [Process code]
)
RETURN

IF( ISFILTERED( 'Disconnected Process code' ),
    COUNTROWS( INTERSECT( SelValue, TestValue )),
    0
)

.

with this result

image
image1500Ă—241 18.7 KB

Here’s your sample file. Process Code Model.pbix (73.3 KB)
I hope this is helpful

3 Likes
3

@upwardD,

Very impressive solution by @Melissa.

Here’s another approach that is worth taking a look at:

  • Brian
3 Likes
4

Wow! Thank you @Melissa. Grateful!