Filter table on pivoted column

Hi kgrafton86 

It sounds like you're trying to achieve dynamic filtering on a pivoted table () based on selections from another table () and specific slicers for conditions.

Given the complexity of your setup and the fact that traditional filtering methods haven't worked for you, I suggest a two-step approach that leverages both Power Query and DAX measures to achieve the desired outcome.

Step 1: Use Power Query to Normalize Your Data

Since your table is pivoted on conditions, it might be beneficial to unpivot this table back to a more normalized form where each row represents a single patient-condition instance along with its metric category (red, yellow, green). This can be done in Power Query.

Go to the Power Query Editor.

Select your table.

Use the "Unpivot Columns" feature on your condition columns to transform them back into a pair of columns, typically and . This will make filtering based on conditions more straightforward.

For more information on unpivoting columns, please refer to this documentation: Unpivot columns.

Step 2: Create a Dynamic DAX Measure for Filtering

After normalizing your data, you can create a DAX measure that dynamically filters the table based on the selected conditions from the slicers. This measure can leverage the comma-delimited string of selected conditions you mentioned.

Create a measure that parses the comma-delimited string into a table of conditions.

Use this table in an expression to dynamically filter the table based on the conditions selected in the slicers.

Here's a simplified example of what the DAX measure might look like:

FilteredMetrics =
VAR SelectedConditions = GENERATESERIES( /* Parse your comma-delimited string into a table */ )
RETURN
CALCULATE(
COUNTROWS('All_Metrics_patient_pivot'),
FILTER(
'All_Metrics_patient_pivot',
'All_Metrics_patient_pivot'[Condition] IN SelectedConditions
)
)

Please adjust the part to correctly parse your comma-delimited string.

Regards,

Nono Chen

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