Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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 !!
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})
)
)
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 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
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})
)
)
@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
@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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
82 | |
65 | |
63 | |
57 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |