topic Re: Filter with OR behavior/dynamic measure based on slicer in DAX Commands and Tips

Filter with OR behavior/dynamic measure based on slicer

palchaw — Mon, 27 Jan 2025 22:00:16 GMT

Hello,

I have a simple table visual as illustrated below. The goal of this table is to show which Codes meet the criteria defined in the three slicers shows on the same page (Metric1 and Metric2 are Between slicers, Status is a simple Dropdown)

Code	Metric1	Metric2	Status
Red	.6	500	A
Green			B
Blue	.4	6000	A

The issue I am running into is when I change the Metric1 or Metric 2 slicers to anything that doesn't include 0 in the range, everything that has a Status of B is filtered out of the table visual.

I'm not sure how a solution would be crafted, but the ideal output table keeps all records with Status B, regardless of what ranges the Metric slicers are adjusted to and only adjusts the records for Status A.

Re: Filter with OR behavior/dynamic measure based on slicer

Shravan133 — Tue, 28 Jan 2025 02:59:02 GMT

Try this:

Metric Filter Measure =
IF(
SELECTEDVALUE('Table'[Status]) = "B",
1,
IF(
'Table'[Metric1] >= MIN('Slicers'[Metric1 Min]) &&
'Table'[Metric1] <= MAX('Slicers'[Metric1 Max]) &&
'Table'[Metric2] >= MIN('Slicers'[Metric2 Min]) &&
'Table'[Metric2] <= MAX('Slicers'[Metric2 Max]),
1,
0
)
)

Add the new measure Metric Filter Measure to the Filters pane of your table visual.
Set the filter condition to show only rows where the measure is equal to 1 (i.e., rows that meet the slicer criteria for Status A or that have Status = B).

Re: Filter with OR behavior/dynamic measure based on slicer

palchaw — Tue, 28 Jan 2025 19:22:24 GMT

In your example, would this involve creating a separate table called "Slicers" that has two columns, one for each Metric, and two records, one with the Max and one with the Min? Is a relationship required between this new table and the main fact table?

Example:

Metric1	Metric2
0	-10,000
1	10,000

Re: Filter with OR behavior/dynamic measure based on slicer

Anonymous — Wed, 29 Jan 2025 08:56:09 GMT

Hi palchaw ,

Could you provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples? Later we can create a measure as below to replace the original calculated column to get the expected result. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

Best Regards