DAX query with sub-query and IF ELSE

Question

New to DAX, I'm trying to create a below DAX query, however, I'm not sure how to go about, I've a measure (selected value by user) using which I want to pass to the query, and using that measure I want to pass dynamic parameters to a filter. Below is what I want to achieve.

TABLE_SUM = CALCULATE(SUM('TABLE'[Total]), FILTER('TABLE', 'TABLE'[Filter_1] = [Selected-val-1] && 'TABLE'[Filter_2] = "CONSTANT1" && 'TABLE'[Filter_3] = "CONSTANT2" && 'TABLE'[Filter_4] IN (IF ([Selected-val-2] =="All", DISTINCT('TABLE'[Filter_4]),[Selected-val-2]))) )

IF all is selected then I want distinct values of 'TABLE'[Filter_4] else I want to pass [Selected-val-2]?

I get is not a valid table error... if I remove If else condition and only apply the true condition inside IN clause, I don't get the error, but it doesn't solve my purpose.

you have an unnecessary ) here: 'TABLE'[Filter_3] = "CONSTANT2") — RADO
it's a typo in the question, I've fixed it. would you know how to go about it? — Kid101

Alexis Olson Alexis Olson · Accepted Answer · 2020-06-03T16:41:45

The reason you're getting the error is that the false result in your IF returns a scalar rather than a table. 'TABLE'[Filter_4] IN <Some Scalar> isn't valid DAX. You might be able to fix this by replacing the last [Selected-val-2] with a single element list, { [Selected-val-2] }.

@RADO is correct that you can refactor that last line.

'TABLE'[Filter_4]
    IN (
        IF (
            [Selected-val-2] == "All",
            DISTINCT ( 'TABLE'[Filter_4] ),
            [Selected-val-2]
        )
    )

Let's look at the two cases: 'TABLE'[Filter_4] has (1) one value or (2) has multiple values (or no values). Then the above reduces to

(1) The condition [Selected-val-2] == "All" is false:

'TABLE'[Filter_4] IN [Selected-val-2]

Since SELECTEDVALUE ( 'TABLE2'[Filter_4], "All" ) is equivalent to

IF ( HASONEVALUE ( 'TABLE2'[Filter_4] ), VALUES ( 'TABLE2'[Filter_4] ), "All" ), since we are in case (1), this reduces to

'TABLE'[Filter_4] IN VALUES ( 'TABLE2'[Filter_4] )

(2) The condition [Selected-val-2] == "All" is true:

'TABLE'[Filter_4] IN  DISTINCT ( 'TABLE'[Filter_4] )

Both of these are basically equivalent to 'TABLE'[Filter_4] IN DISTINCT ( 'TABLE'[Filter_4] ).

DAX query with sub-query and IF ELSE

1 Answers