The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
Hi,
Thank you for your message, and please try something like below.
|| function might help.
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 ) )
)
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 )
)
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.
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.
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 ) )
)
User | Count |
---|---|
12 | |
9 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
14 | |
9 | |
7 |