DAX measure If statement help

[rohitk1361]

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

[rohitk1361]

the logic worked earlier but now it stopped working

[Anonymous]

Hi,

Check to see if any other filters are being applied to the Tabel source data.

Best Regards,

Wisdom Wu

[Anonymous · Accepted solution]

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.

[lbendlin]

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.