DAX Measure, Switch with multiple criteria-CodePudding

Thank you for you help in advance.

I am trying to create a measure to sum the adjusted value of certain column depending on the date:

The logic is:

If Date is before 06/22/2022, Then: For Sales Code "A0001", "A0003" and "A0004", adjust their Sales Count (in another column) by *3

For Sales Code "B0001", adjust their Sales Count (in another column) by *4,

For Sales Code "C0001", adjust their Sales Count (in another column) by *5,

Else Sales Count stay the same

This is what I have so far but its not calculating correctly

Measure =
SUMX('TABLE’,
            SWITCH (
                'TABLE'[Date] < DATE(2022,06,22)
                'TABLE'[Sales Code] in {"A0001", "A0003", "A0004"}, 'TABLE'[Sales Count] * 3,
                'TABLE'[Sales Code] in {"B0001"}, 'TABLE'[Sales Count] * 4,
                'TABLE'[Sales Code] in {"C0001"}, 'TABLE'[Sales Count] * 5,
             'TABLE'[Sales Count]
    )
)

CodePudding user response：

First of all, you've missed the first argument of SWITCH() function. It expects expression with TRUE/FALSE result at this place.

There is a hint to use TRUE() as first argument, but it's not a universal solution and should be used carefully. To know more about potential problems of using this hint read this.

Then I would like to remind you that you can use && (AND) and || (OR) to write complex conditions.

So, the solution of your problem looks like:

Measure = 
SUMX(
    'TABLE',
    SWITCH (
        TRUE,
        'TABLE'[Date] < DATE(2022,06,22)
            && 'TABLE'[Sales Code] in {"A0001", "A0003", "A0004"},
        'TABLE'[Sales Count] * 3,
        'TABLE'[Date] < DATE(2022,06,22)
            && 'TABLE'[Sales Code] in {"B0001"},
        'TABLE'[Sales Count] * 4,
        'TABLE'[Date] < DATE(2022,06,22)
            && 'TABLE'[Sales Code] in {"C0001"},
        'TABLE'[Sales Count] * 5,
        'TABLE'[Sales Count]
    )
)

For those who are new with SWITCH() function I would advice to visit this page.