Forum Discussion
Excluding rows from Calculated table
Hi All,
I need to create a new table with DAX, excluding rows where STATE = STATE1 AND BRAND = BRAND1
Here is my dummy data: Table1
| STORE | STATE | BRAND | ANSWER |
| STORE1 | STATE1 | BRAND1 | 1 |
| STORE1 | STATE1 | BRAND2 | 0 |
| STORE1 | STATE1 | BRAND3 | 1 |
| STORE2 | STATE2 | BRAND1 | 1 |
| STORE2 | STATE2 | BRAND2 | 1 |
| STORE2 | STATE2 | BRAND3 | 0 |
| STORE3 | STATE1 | BRAND1 | 0 |
| STORE3 | STATE1 | BRAND2 | 1 |
| STORE3 | STATE1 | BRAND3 | 0 |
I know I have to use CALCULATETABLE but I don't know how to filter out both conditions.
This is did I have so far:
Table2 = CALCULATETABLE( SUMMARIZE( Table1 , Table1[STORE], Table1[STATE], Table1[BRAND] ),
I loaded your data into a table 'Table1'
Then created a Table2 using the measure
Table2 = FILTER ( Table1, NOT ( Table1[STATE] = "State1" && Table1[BRAND] = "Brand1" ) )
Leaving a table with no row that is a State1 Brand1 row. Was that what you were looking for?
3 Replies
I loaded your data into a table 'Table1'
Then created a Table2 using the measure
Table2 = FILTER ( Table1, NOT ( Table1[STATE] = "State1" && Table1[BRAND] = "Brand1" ) )
Leaving a table with no row that is a State1 Brand1 row. Was that what you were looking for?
jdbuchanan71 thanks that 's exactly what I was looking for. I wasn't sure how to use the NOT function.
Cheers
Hii RobinDeFal
You want like this.....?
Table2= CALCULATETABLE(Table1,FILTER(Table1,Table1[STATE]<>"STATE1"),Table1[BRAND]<>"BRAND1")