Forum Discussion
DAX measure If statement help
I am trying to write a dax with if statement. I want to join historical data with current year data that comes from different source, below is sample table with output and desired output.
in the visual i want to use scenario as a selection, so user can select scenario B, but still see historical 2023 data with scenario A, hence i added removefilter condition in first if .
my condition is
if (left(max(year) = "2023", calculate(sum(amt), source = ABC, removefilters(scenario)), calculate(sum(amt), source = CY)
| Scenario | Year | Source | Category | Amt | Output shown | Desired Output |
| A | 2023 | ABC | M | 40 | 40 | 40 |
| A | 2023 | ABC | N | 25 | 25 | |
| A | 2024 | ABC | P | 70 | ||
| B | 2023 | XYZ | M | 40 | ||
| B | 2024 | CY | P | 80 | 80 | 80 |
Hi rohitk1361 ,
Based on my testing, Removefilters function only clear filters from the specified tables or columns. Don’t clear the slicer selected value. Please try the following methods:
1.Create the sample table.
2.Drag the table scena column into the slicer visual.
3.Create the measure to calculate the values.
Measure = var _selected = SELECTEDVALUE('Table Scena'[Scenario]) var _scenario = SELECTEDVALUE('Table'[Scenario]) RETURN IF ( _scenario = _selected, CALCULATE( SUM('Table'[Amt]), 'Table'[Source] = "CY" // REMOVEFILTERS('Table Scena'[Scenario]) ), IF(LEFT(MAX('Table'[Year]), 4) = "2023", CALCULATE( SUM('Table'[Amt]), 'Table'[Source] = "ABC", REMOVEFILTERS('Table Scena') ) ) )4.Select the scenario in the slicer visual. The result is shown below.
You can also view the following link to learn more information.
REMOVEFILTERS function (DAX) - DAX | Microsoft Learn
Solved: DAX Measure ignore filter/slicer selection - Microsoft Fabric Community
ALL function (DAX) - DAX | Microsoft Learn
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- lbendlin
Super User
To do this you need a disconnected table for the scenarios. Use that table to feed your slicer, and then use a measure as a visual filter for your resulting table. You cannot implement this "OR" logic inside a single table.
Hi rohitk1361 ,
Based on my testing, Removefilters function only clear filters from the specified tables or columns. Don’t clear the slicer selected value. Please try the following methods:
1.Create the sample table.
2.Drag the table scena column into the slicer visual.
3.Create the measure to calculate the values.
Measure = var _selected = SELECTEDVALUE('Table Scena'[Scenario]) var _scenario = SELECTEDVALUE('Table'[Scenario]) RETURN IF ( _scenario = _selected, CALCULATE( SUM('Table'[Amt]), 'Table'[Source] = "CY" // REMOVEFILTERS('Table Scena'[Scenario]) ), IF(LEFT(MAX('Table'[Year]), 4) = "2023", CALCULATE( SUM('Table'[Amt]), 'Table'[Source] = "ABC", REMOVEFILTERS('Table Scena') ) ) )4.Select the scenario in the slicer visual. The result is shown below.
You can also view the following link to learn more information.
REMOVEFILTERS function (DAX) - DAX | Microsoft Learn
Solved: DAX Measure ignore filter/slicer selection - Microsoft Fabric Community
ALL function (DAX) - DAX | Microsoft Learn
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
the logic worked earlier but now it stopped working
