cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HJK
Frequent Visitor

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

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

TypeNumber
A1
A2
A3
B1
B2
B3
C1
C2
C3

 

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

TypeNumber
A1
A2
B1
B2
B3
C1
C2
C3
1 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 ) )
)

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_0-1669264215703.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you.

 

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

 

Hi,

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

 

Jihwan_Kim_0-1669267674221.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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?

 

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 ) )
)

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!