Filter table on pivoted column

[kgrafton86]

I am trying to filter one table based on another, where the value(s) I am trying to filter on are pivoted. I have the AllPatients table that is filtering properly and has separate slicers for each of the conditions. That table looks like this:

I am trying to filter another table, All_Metrics_patient_pivot that is pivoted on the conditions and looks like this:

Here is a sample of the dashboard I am building. The top and left visuals use the AllPatients table, with the top showing how many patients have each count of conditions (i.e., patients with 5-6 conditions are likely to have more issues than patients with just 1 condition) and the left showing a count of how many patients have each condition. The metrics visual on the right is what I'm having trouble with. It uses the All_Metrics_patient_pivot table to count how many patients with each condition are in the red, yellow, and green categories of each metric. The top and left currently filter correctly with the filter I am using, while the right does not filter at all.

I should also add that each condition has its own toggle switch.

I have tried a number of ways to filter the pivoted table based on the condition filters, and nothing is working. How would I go about this? If either will help, in the process of trying various things I have a metric that gives me a comma-delimited string of selected conditions (e.g., "CHF", "HLD") and a table that is one column containing a list of the conditions that will filter the All_Metrics_patient_pivot table, but I haven't been able to filter that by the slicers either. I'm starting to suspect my approach is wrong, and maybe the answer is in PowerQuery and not using measures? Any help would be appreciated.

[v-nuoc-msft]

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.