I want to exclude a specific value after CROSSJOIN two tables.

[HJK]

Hi

I want to exclude a specific value after CROSSJOIN two tables.

See example below.

How can I do it?

1. Table A

Type

A

B

C

2. Table B

Number

1

2

3

3. Cross join

Type	Number
A	1
A	2
A	3
B	1
B	2
B	3
C	1
C	2
C	3

4. I want to exclude "3" from type "A" and 

Type	Number
A	1
A	2
B	1
B	2
B	3
C	1
C	2
C	3

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

New table =
FILTER (
    GENERATE ( 'Table A', 'Table B' ),
    NOT ( 'Table A'[Type] = "A"
        && 'Table B'[Number] = 3 )
)

[HJK]

Thank you.

How do I get rid of both 2 and 3 from type A?

[Jihwan_Kim]

Hi,

Thank you for your feedback, and please check the attached file down below.

[HJK]

Thank you. really helpful

sorry but i have one more question

The OR function can only filter two things.
How can I filter more than 3?

For example, what if i need to remove 1,2,3 out of 1, 2, 3, 4 of type A?

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message, and please try something like below.

|| function might help.

Or (||) – DAX Guide

New table =
FILTER (
    GENERATE ( 'Table A', 'Table B' ),
    NOT ( 'Table A'[Type] = "A"
        && ( 'Table B'[Number] = 3
        || 'Table B'[Number] = 4
        || 'Table B'[Number] = 5
        || 'Table B'[Number] = 6 ) )
)