Dynamically Filter Based on Column

[Foxxon28]

Dear reader,

I've been trying to tackle an issue for a while now without much succes. 

What I am trying to achieve is to dynamically filter a max month, based on sliced values.

I want to dynamically pick a MAX month, based on which value is selected in a slicer. I have created previous forum posts for this, without much luck.

In an effort to fix it on my own I have tried a new method, but am stick on the exectution.

I will give you some sample data.

Date (DDMMYYYY)	Channel	Amount	Indicator
01-01-2024	A	10	1
01-01-2024	B	12	1
01-02-2024	A	5	1
01-02-2024	B	15	1
01-03-2024	B	20	0

I have added an [Indicator] column that checks if the [Date] of that row is higher(>) than the MAX Date from [Channel] = "A" (in this example (01-02-2024).

I want to create a visual level filter that returns 1, 0, based on the following conditions:

If NOTHING is filtered, I want to filter where [Indicator] = 1

if [Channel] CONTAINS "A", I want to filter where [Indicator] = 1

If [Channel] DOES NOT contain "A", I want to filter where [Indicator] = 1 AND 0

If you have any other suggestions, please let me know.

Kind regards,

Daniël

[johnbasha33 · Accepted solution]

@Foxxon28 

1. **Calculate Max Date for Channel A**:
- Create a measure to calculate the maximum date for Channel A. This measure will be used as a reference point for filtering.
```DAX
MaxDate_ChannelA = CALCULATE(MAX('YourTable'[Date]), 'YourTable'[Channel] = "A")
```

2. **Create Indicator Column**:
- Create an Indicator column that checks if the Date is higher than the Max Date for Channel A.
```DAX
Indicator = IF('YourTable'[Date] > [MaxDate_ChannelA], 1, 0)
```

3. **Define Visual Level Filters**:
- Create visual-level filters based on your conditions:
- If NOTHING is filtered, filter where Indicator = 1.
- If Channel CONTAINS "A", filter where Indicator = 1.
- If Channel DOES NOT contain "A", filter where Indicator = 1 AND 0.

You can use these filters to dynamically control which rows are displayed based on the slicer selections.

4. **Alternative Approach**:
- Instead of using visual-level filters, you can also create a measure that dynamically calculates the Indicator value based on slicer selections.
This measure can evaluate the slicer context and return the appropriate Indicator value accordingly.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[johnbasha33 · Accepted solution]

@Foxxon28 

1. **Calculate Max Date for Channel A**:
- Create a measure to calculate the maximum date for Channel A. This measure will be used as a reference point for filtering.
```DAX
MaxDate_ChannelA = CALCULATE(MAX('YourTable'[Date]), 'YourTable'[Channel] = "A")
```

2. **Create Indicator Column**:
- Create an Indicator column that checks if the Date is higher than the Max Date for Channel A.
```DAX
Indicator = IF('YourTable'[Date] > [MaxDate_ChannelA], 1, 0)
```

3. **Define Visual Level Filters**:
- Create visual-level filters based on your conditions:
- If NOTHING is filtered, filter where Indicator = 1.
- If Channel CONTAINS "A", filter where Indicator = 1.
- If Channel DOES NOT contain "A", filter where Indicator = 1 AND 0.

You can use these filters to dynamically control which rows are displayed based on the slicer selections.

4. **Alternative Approach**:
- Instead of using visual-level filters, you can also create a measure that dynamically calculates the Indicator value based on slicer selections.
This measure can evaluate the slicer context and return the appropriate Indicator value accordingly.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Foxxon28]

@johnbasha33 could you still write an example for me :)?

Kind regards,

Daniël

[Foxxon28]

@johnbasha33 Would you be so kind to give a DAX example for point 3.

And perhaps also for your alternative approach, as I've tried this, but wasn't able to achieve this.

Thanks! 🙂

Daniël

[johnbasha33 · Accepted solution]

@Foxxon28  

FilteredValue :=
VAR ChannelContainsA = CONTAINSSTRING(SELECTEDVALUE('YourTable'[Channel]), "A")
VAR IsFiltered = NOT ISBLANK(SELECTEDVALUE('YourTable'[Channel]))

RETURN
IF(
NOT IsFiltered,
CALCULATE(SUM('YourTable'[Value]), 'YourTable'[Indicator] = 1),
IF(
ChannelContainsA,
CALCULATE(SUM('YourTable'[Value]), 'YourTable'[Indicator] = 1),
CALCULATE(SUM('YourTable'[Value]), 'YourTable'[Indicator] IN {1, 0})
)
)

[amitchandak]

@Foxxon28 , refer this for the latest value for each channel

Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[Foxxon28]

@amitchandak thank you for the swift reply, however this is not what I am looking for.

The Values are used for cumulative numbers. Depending on what slicer value you select, it should dynamically calculate up untill a specific month.