How do I append the results of multiple filters for the same table?

[Anonymous]

Hello guys, I am having an issue with some dynamic filtering on a table, and I am asking for your help to make this work.

So here is the problem described:

I have a Table A, for which I am applying 2 different filtering measures:

Measure 1 = Calculate(DistinctCount(Table A [col X], Filter(Table A, expression 1)));

Measure 2 = Calculate(DistinctCount(Table A [col X], Filter(Table A, expression 2)));

What I would like to do:

Measure 3 = Calculate(DistinctCount(Table A [col X], Append(Filter 1 Result, Filter 2 Result)); - or something like this.

The reason I want to do this:

I have to count the distinct value from both of the result sets and I cannot reunite the logic inside expression 1 and 2, and I am trying to rule out rows that could appear as a result for both expressions.

Am I overthinking something, or is there no way? If there is, can anyone suggest one please?

[v-chuncz-msft]

@Anonymous,

It seems that you may simply use the logical OR operator (||).

[Anonymous]

@v-chuncz-msft, it would be really cool for it to be that easy, but the problem is that the 2 expressions are pointed at 2 different contexts of the same table:

M1's filter: Filter(All(Table A), expression 1)));

M2's filter: Filter(Table A, expression 2)));

Which means that for the expression 1 and 2, I cannot use a simple OR, because the ALL refers to the whole table, whereas the second refers to the selected values based on the filters set to a report level.

Or is there a way to implement an OR statement prior to that?

[v-chuncz-msft]

@Anonymous,

You may add UNION Function.

[Anonymous · Accepted solution]

@v-chuncz-msft thank you for looking into my matters!

In the end I managed to get a solution to my problem. I had to use a function I've never used before, but it could be useful to anybody out there so I'm going to explain what I did:

I took the results from the two filterings and I assigned them to a VAR each using the CALCULATETABLE function. I then used the COUNTROWS function on a DISTINCT of the two VARs' UNION. It worked like a charm! 

I hope this comes in handy to anyone out there!